﻿'Description:批次工区协作类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Yangsj
'create Date:2017-4-11
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient

Public Class uLMSDispatchBusiness
    Implements uLMSDispatchIBusiness
    Private threadLock As Object = New Object()
    Private m_ConCurrentinfoObj As New uLMSContainercurrentinfoBusiness
    Private m_ReportBusiness As New uLMSWorkReportBusiness
    Private m_CommonBusiness As New uLMSCommonBusiness
    Private toAppID_RB As String = System.Configuration.ConfigurationManager.AppSettings("ToAppID_RB").ToString() '数据中心推送目的ID（热表）

    ''' <summary>
    ''' yuxj集件大阶段时间调整查询sql
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCollectContainer(ByVal para As Dictionary(Of String, String)) As DTO.uMESProcessResult Implements uLMSDispatchIBusiness.GetCollectContainer
        Dim strsql As String = <string>
                                    SELECT u.attributevalue machinename,m.mfgordername,ep.containerno,ep.enginepartplanid,ep.plannedstartdate,wfs.workflowstepname,
                                    ep.plannedfinishdate,ep.workflowstepid,ep.CompleteStepId FROM enginepartplan ep
                                    LEFT JOIN mfgorder m ON m.mfgorderid=ep.mfgorderid
                                    INNER JOIN Userattribute u ON u.parentid=m.mfgorderid AND u.userattributename='发动机'
                                    LEFT JOIN workflowstep wfs ON wfs.workflowstepid=ep.workflowstepid
                                    WHERE ep.usecompleted=0
                               </string>
        If para.ContainsKey("ContainerNo") Then
            strsql += String.Format(" and ep.ContainerNo like '%{0}%'", para("ContainerNo"))
        End If

        If para.ContainsKey("MfgorderName") Then
            strsql += String.Format(" and m.mfgordername like '%{0}%'", para("MfgorderName"))
        End If

        If para.ContainsKey("MachineStart") Then
            strsql += String.Format(" and u.attributevalue like '%{0}%'", para("MachineStart"))
        End If

        If para.ContainsKey("WorkFlowStepName") Then
            strsql += String.Format(" and wfs.WorkFlowStepName like '%{0}%'", para("WorkFlowStepName"))
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strsql
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取工区协作数据
    ''' create:Wangjh
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks>REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME, modify by tianFW 2018年5月17日 单件单卡取C.TOGETHERFLAG 数量批取C.PRODUCTNAME</remarks>
    Public Function GetContainerCooperationData(ByVal QueryCondition As System.Collections.Generic.Dictionary(Of String, String)) As DTO.uMESProcessResult Implements IBusiness.uLMSDispatchIBusiness.GetContainerCooperationData
        Dim strSelect As String =
          <string>
             SELECT C.PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE,C.TOGETHERFLAG,
             C.CONTAINERTYPE,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.PLANQTY,C.OriginalFactoryID,
             C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,(c.productname||'/'||c.containerno) ProductBatch, 
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strSelect)
        'sb.AppendLine("REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME,")
        sb.AppendLine("SUM(C.QTY) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,NULL WORKREPORTNUM,'' FaContianerID,'' ShipID")
        sb.AppendLine(" FROM CONTAINERCURRENTINFO C ")
        'sb.AppendLine(" LEFT JOIN containerworkcenterdispatch cwd ON cwd.containerid=c.containerid") 'create:Wangjh 2017-7-7
        If QueryCondition("Status") = "0" Then '待接收
            sb.AppendLine(" LEFT JOIN (" &
                   " SELECT CWD.CONTAINERID,CWD.TOWORKCENTERID FROM containerworkcenterdispatch cwd" &
                   "  WHERE CWD.ISUSE=0 and cwd.recvdate is null" &
                   "  GROUP BY CWD.CONTAINERID,CWD.TOWORKCENTERID" &
                   "  ) A ON A.CONTAINERID=C.CONTAINERID") 'add:Wangjh 2017-8-9

        End If

        Dim sbCondition As String = GetCondition(QueryCondition)
        sbCondition += " AND C.SPLITNUM=0 AND C.ISINSTORE=0 AND C.CONTAINERSTATUS=1" 'tianFW 2017-07-07 删除 AND C.DISPATCHNUM=0  '2017/11/2 ADD C.CONTAINERSTATUS=1 '
        If QueryCondition.Keys.Contains("Status") Then
            If QueryCondition("Status") = "0" Then '待接收
                sbCondition += String.Format("  AND A.TOWORKCENTERID = '{0}' AND C.WORKCENTERDISPATCHNUM =1", QueryCondition("WorkCenterID")) 'UPDATE:Wangjh 2017-7-7 改为CWD
            ElseIf QueryCondition("Status") = "3" Then '已接收
                sbCondition += String.Format("  AND C.TOWORKCENTERID = '{0}' AND C.WORKCENTERDISPATCHNUM =0", QueryCondition("WorkCenterID"))
            ElseIf QueryCondition("Status") = "1" Then '待申请
                sbCondition += String.Format("   AND C.FACTORYID='{0}' AND C.WORKCENTERID = '{1}' AND C.WORKCENTERDISPATCHNUM <>1", QueryCondition("FactoryID"), QueryCondition("WorkCenterID"))
            ElseIf QueryCondition("Status") = "2" Then '已申请
                sbCondition += String.Format("  AND (C.WORKCENTERID = '{0}' AND (C.WORKCENTERDISPATCHNUM =1 OR (C.TOWORKCENTERID IS NOT NULL AND C.Isdispatch=0)) )", QueryCondition("WorkCenterID")) 'update:Wangjh 2017-7-7  AND C.WORKCENTERDISPATCHID IS NOT NULL AND C.TOWORKCENTERID IS NOT NULL改为C.WORKCENTERDISPATCHNUM =1
            End If
        End If

        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE='7'")
        sb.AppendLine(" GROUP BY C.PRODUCTID, C.PRODUCTNAME, C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID, C.WORKFLOWID, C.WORKFLOWSTEPSEQUENCE, C.SPECNO,") '将 C.SEQUENCE更换为C.WORKFLOWSTEPSEQUENCE tianFW 2017-06-03
        sb.AppendLine(" C.TOGETHERFLAG, C.CONTAINERTYPE,C.MFGORDERNAME,C.PLANQTY, C.PLANNEDSTARTDATE, C.PLANNEDCOMPLETIONDATE,")
        sb.AppendLine(" C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,c.containerno,C.OriginalFactoryID ")
        sb.AppendLine(" UNION ")
        sb.AppendLine(strSelect)
        'sb.AppendLine("SUBSTR(C.CONTAINERNAME, LENGTH(C.PRODUCTNAME) + 2) TOGETHERFLAGNAME,")
        sb.AppendLine("C.QTY AS WIPQTY , C.CONTAINERID,C.CONTAINERNAME,C.WORKREPORTNUM,C.CONTAINERID AS FaContianerID,C.ShipID")
        sb.AppendLine(" FROM CONTAINERCURRENTINFO C ")
        If QueryCondition("Status") = "0" Then '待接收
            sb.AppendLine(" LEFT JOIN (" &
                         " SELECT CWD.CONTAINERID,CWD.TOWORKCENTERID FROM containerworkcenterdispatch cwd" &
                         "  WHERE CWD.ISUSE=0" &
                         "  GROUP BY CWD.CONTAINERID,CWD.TOWORKCENTERID" &
                         "  ) A ON A.CONTAINERID=C.CONTAINERID") 'add:Wangjh 2017-8-9
        End If

        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE<>'7' ")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Dim sonSql As String = ""
        If QueryCondition("Status") = "0" Then '待接收
            sonSql = "SELECT C.* FROM CONTAINERCURRENTINFO C " &
                                    " LEFT JOIN (" &
                                    " SELECT CWD.CONTAINERID,CWD.TOWORKCENTERID FROM containerworkcenterdispatch cwd" &
                                    "  WHERE CWD.ISUSE=0" &
                                    "  GROUP BY CWD.CONTAINERID,CWD.TOWORKCENTERID" &
                                    "  ) A ON A.CONTAINERID=C.CONTAINERID"
        Else
            sonSql = "SELECT C.* FROM CONTAINERCURRENTINFO C "
        End If
        sbCondition = sonSql + sbCondition  'update:Wangjh 2017-8-9 关联协作表


        Return DatabaseHelper.GetContainerForPage(paraPage, sbCondition)

    End Function

    ''' <summary>
    ''' 获取选定流水卡已申请的工序
    ''' create:Wangjh
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkCenterDisPatchSpecList(ByVal QueryCondition As System.Collections.Generic.Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSDispatchIBusiness.GetWorkCenterDisPatchSpecList
        Dim sql As New StringBuilder
        sql.AppendLine("SELECT WS.WORKFLOWSTEPID,WS.WORKFLOWSTEPNAME, WS.SEQUENCE,WS.WORKFLOWSTEPID,C.CONTAINERNAME,CWD.EMPLOYEEID," &
                      " CWD.CREATEDATE,CWD.TOWORKCENTERID,WC.WORKCENTERNAME,CWD.CONTAINERWORKCENTERDISPATCHID,E.FULLNAME, " &
                      " CWD.RECVDATE,CWD.RECVEMPLOYEEID,CWD.COMPLETEDATE,CWD.estimatedsenddate FROM CONTAINERWORKCENTERDISPATCH CWD")
        sql.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CWD.WORKFLOWSTEPID")
        sql.AppendLine("LEFT JOIN WORKFLOW W ON WS.WORKFLOWID = W.WORKFLOWID")
        sql.AppendLine("LEFT JOIN CONTAINER C ON C.CONTAINERID = CWD.CONTAINERID")
        sql.AppendLine("LEFT JOIN WORKCENTER WC ON CWD.TOWORKCENTERID=WC.WORKCENTERID")
        sql.AppendLine("LEFT JOIN EMPLOYEE E ON E.EMPLOYEEID=CWD.EMPLOYEEID")
        sql.AppendLine("WHERE CWD.CONTAINERWORKCENTERDISPATCHID IS NOT NULL AND CWD.ISUSE=0 ")
        If QueryCondition.Keys.Contains("WorkflowID") = True Then
            sql.AppendFormat(" AND W.WORKFLOWID='{0}'", QueryCondition("WorkflowID"))
        End If
        If QueryCondition.Keys.Contains("Sequence") = True Then
            sql.AppendFormat(" AND WS.SEQUENCE>={0}", QueryCondition("Sequence"))
        End If
        'If QueryCondition.Keys.Contains("EmployeeID") = True Then
        '    sql.AppendFormat(" AND CWD.EmployeeID='{0}'", QueryCondition("EmployeeID"))
        'End If
        If QueryCondition.Keys.Contains("ContainerID") = True Then
            sql.AppendFormat(" AND C.ContainerID IN '{0}'", QueryCondition("ContainerID"))
        End If
        If QueryCondition.Keys.Contains("ToWorkCenterID") = True Then
            sql.AppendFormat(" AND CWD.ToWorkCenterID = '{0}'", QueryCondition("ToWorkCenterID"))
        End If 'RECVEMPLOYEEID
        If QueryCondition.Keys.Contains("RecvEmployeeID") = True Then
            If QueryCondition("RecvEmployeeID") = "0" Then '接收人不存在
                sql.AppendLine(" AND CWD.RecvEmployeeID IS NULL")
            ElseIf QueryCondition("RecvEmployeeID") = "1" Then
                sql.AppendLine(" AND CWD.RecvEmployeeID IS NOT NULL")
            End If
        End If
        sql.AppendLine(" ORDER BY WS.SEQUENCE")
        Dim strsql As String = sql.ToString
        Dim result As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)

        Return result
    End Function

    ''' <summary>
    ''' 获取生产派工表信息
    ''' create tianFW
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepDispatchSpecList(ByVal QueryCondition As System.Collections.Generic.Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSDispatchIBusiness.GetStepDispatchSpecList
        Dim sql As New StringBuilder
        sql.AppendLine("SELECT CSD.WORKFLOWSTEPID,WS.WORKFLOWSTEPNAME, WS.SEQUENCE,C.CONTAINERNAME,CSD.CONTAINERID,CSD.EMPLOYEEID,CSD.CREATEDATE,CSD.WORKCENTERID,CSD.PLANNEDSTARTDATE,")
        sql.AppendLine("CSD.PLANNEDFINISHDATE,CSD.WORKCENTERID,CSD.QTY-NVL(A.CSDREPORTLOSSQTY,0) DISQTY,RD.DESCRIPTION || '(' || RD.RESOURCENAME || ')' RESOURCENAME,RD.RESOURCEID,T.TEAMNAME,CSD.TEAMID,WC.WORKCENTERNAME,CSD.CONTAINERSTEPDISPATCHID,NVL(E2.FULLNAME,'多人') AS WORKNAME,")
        'sql.AppendLine("E.FULLNAME,CSD.WORKERID,C.QTY - NVL(K.HAVEDDISQTY,0) AS CANDISNUM,C.QTY AS WIPQTY,CSD.PRIORITYCODE,NVL(A.CSDREPORTQTY,0) CSDREPORTQTY  ")
        'modify zhunan 2021.12.13 修复报工数为负数bug
        sql.AppendLine("E.FULLNAME,CSD.WORKERID,CASE WHEN C.QTY - NVL(K.HAVEDDISQTY,0) < 0 THEN 0 ELSE C.QTY - NVL(K.HAVEDDISQTY,0) END CANDISNUM,C.QTY AS WIPQTY,CSD.PRIORITYCODE,NVL(A.CSDREPORTQTY,0) CSDREPORTQTY  ")
        sql.AppendLine(" FROM CONTAINERSTEPDISPATCH CSD")
        sql.AppendLine("LEFT JOIN EMPLOYEE E ON E.EMPLOYEEID = CSD.EMPLOYEEID ")
        sql.AppendLine("LEFT JOIN EMPLOYEE E2 ON E2.EMPLOYEEID = CSD.WORKERID")
        sql.AppendLine("LEFT JOIN RESOURCEDEF RD ON RD.RESOURCEID = CSD.RESOURCEID ")
        sql.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSD.WORKFLOWSTEPID")
        sql.AppendLine("LEFT JOIN WORKFLOW WF ON WF.WORKFLOWID = WS.WORKFLOWID")
        sql.AppendLine("LEFT JOIN CONTAINER C ON C.CONTAINERID = CSD.CONTAINERID")
        sql.AppendLine("LEFT JOIN WORKCENTER WC ON WC.WORKCENTERID = CSD.WORKCENTERID ")
        sql.AppendLine("LEFT JOIN TEAM T ON T.TEAMID = CSD.TEAMID")
        sql.AppendLine("LEFT JOIN(SELECT SUM(CSD_.QTY) HAVEDDISQTY,CSD_.CONTAINERID,CSD_.WORKFLOWSTEPID ")
        sql.AppendLine("FROM CONTAINERSTEPDISPATCH CSD_ WHERE CSD_.ISUSE=0")
        sql.AppendLine("GROUP BY CSD_.CONTAINERID,CSD_.WORKFLOWSTEPID) K ON K.CONTAINERID = CSD.CONTAINERID AND K.WORKFLOWSTEPID=CSD.WORKFLOWSTEPID")
        sql.AppendLine("LEFT JOIN (SELECT SUM(CSW.QTY) CSDREPORTQTY,SUM(CSW.LOSSQTY) CSDREPORTLOSSQTY,CSW.CONTAINERSTEPDISPATCHID")
        sql.AppendLine("FROM CONTAINERSTEPWORKREPORT CSW WHERE CSW.ISUSE=0")
        sql.AppendLine("GROUP BY CSW.CONTAINERSTEPDISPATCHID) A ON A.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID")
        sql.AppendLine("WHERE CSD.ISUSE = 0")
        If QueryCondition.ContainsKey("DispatchChangeNow") Then '派工修改页面显示报工数少于派工数的记录
            If QueryCondition("ReportStatus") = "1" Then
                sql.AppendLine("AND NVL(A.CSDREPORTQTY, 0) = 0")
            ElseIf QueryCondition("ReportStatus") = "2" Then
                sql.AppendLine("AND NVL(A.CSDREPORTQTY, 0) > 0")
            End If
        End If
        If QueryCondition.Keys.Contains("WorkCenterID") = True Then
            sql.AppendFormat(" AND CSD.WORKCENTERID='{0}'", QueryCondition("WorkCenterID"))
        End If
        If QueryCondition.Keys.Contains("WorkflowID") = True Then
            sql.AppendFormat(" AND WF.WORKFLOWID='{0}'", QueryCondition("WorkflowID"))
        End If
        If QueryCondition.Keys.Contains("Sequence") = True Then
            sql.AppendFormat(" AND WS.SEQUENCE>={0}", QueryCondition("Sequence"))
        End If
        If QueryCondition.Keys.Contains("EmployeeID") = True Then
            sql.AppendFormat(" AND CSD.EMPLOYEEID='{0}'", QueryCondition("EmployeeID"))
        End If
        If QueryCondition.Keys.Contains("ContainerIDs") = True Then
            sql.AppendFormat(" AND CSD.CONTAINERID IN ({0})", QueryCondition("ContainerIDs"))
        End If
        If QueryCondition.Keys.Contains("StepDispatch") Then
            If QueryCondition.ContainsKey("TeamID") Then
                sql.AppendFormat(" AND CSD.TeamID ='{0}'", QueryCondition("TeamID"))
            End If
        End If
        If QueryCondition.ContainsKey("WorkflowStepID") Then
            sql.AppendFormat(" AND CSD.WORKFLOWSTEPID ='{0}'", QueryCondition("WorkflowStepID"))
        End If
        sql.AppendLine(" ORDER BY C.CONTAINERNAME,WS.SEQUENCE ")
        Dim strsql As String = sql.ToString
        Dim result As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        Return result
    End Function

    ''' <summary>
    ''' 获取批次班组派工数据V2.0
    ''' create:Wangjh
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks>REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME, modify by tianFW 2018年5月17日 单件单卡取C.TOGETHERFLAG 数量批取C.PRODUCTNAME</remarks>
    Public Function GetContainerTeamDispatch(ByVal QueryCondition As System.Collections.Generic.Dictionary(Of String, String)) As DTO.uMESProcessResult Implements IBusiness.uLMSDispatchIBusiness.GetContainerTeamDispatch
        Dim strSelect As String =
     <string>
             SELECT C.PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE,C.TOGETHERFLAG,
             C.CONTAINERTYPE,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.PLANQTY,
             C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,(c.productname||'/'||c.containerno) ProductBatch, 
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strSelect)
        'sb.AppendLine("REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME,")
        sb.AppendLine("COUNT(DISTINCT C.CONTAINERID) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,NULL WORKREPORTNUM,'' FaContianerID,''TeamDispatchID,''TEAMNAME") 'add:Wawngjh 2017-7-12 增加C.TEAMNAME
        sb.AppendLine(" FROM CONTAINERCURRENTINFO C ")
        '燃烧分厂需要
        sb.AppendLine(" LEFT JOIN (" &
                      " SELECT  CTD.WORKCENTERID,CTD.TEAMID,CTD.CONTAINERID,CTD.CONTAINERTEAMDISPATCHID,CTD.WORKFLOWSTEPID FROM CONTAINERTEAMDISPATCH CTD " &
                    "  LEFT JOIN CONTAINER CO ON CO.CONTAINERID=CTD.CONTAINERID" &
                    "  WHERE   CTD.ISUSE=0")
        sb.AppendFormat(" AND CTD.WORKCENTERID='{0}'", QueryCondition("WorkCenterID"))
        sb.AppendLine(" ) A ON A.CONTAINERID  =C.CONTAINERID")
        '
        'Dim sbCondition As String = " LEFT JOIN CONTAINERWORKCENTERDISPATCH CWD ON CWD.CONTAINERWORKCENTERDISPATCHID=C.WORKCENTERDISPATCHID "'Wangjh 2017-7-21 注释
        Dim sbCondition As String = ""
        sbCondition += GetCondition(QueryCondition)
        sbCondition += String.Format(" AND C.Containerstatus=1 AND C.SPLITNUM=0 AND C.ISINSTORE=0 AND (C.WORKCENTERID='{0}' or C.toWORKCENTERID='{0}' OR C.STEPWORKCENTERIDS LIKE '%{1}%')", QueryCondition("WorkCenterID"), QueryCondition("WorkCenterID"))
        If QueryCondition.Keys.Contains("Status") Then
            If QueryCondition("Status") = "1" Then '未派工到班组
                If QueryCondition.ContainsKey("AsseCheck") Then '工序班组派工
                    sbCondition += " AND C.TeamDispatchNum =0 " '批次不存在班组派工
                    'sbCondition += String.Format(" OR (C.TeamDispatchNum =1 AND A.CONTAINERID IS NULL))") '批次在本工区不存在班组派工
                Else '批次班组派工
                    sbCondition += "AND C.DISPATCHNUM=0 AND C.TeamDispatchNum =0 "
                End If
            ElseIf QueryCondition("Status") = "2" Then '已派工到班组
                If QueryCondition.ContainsKey("AsseCheck") Then '工序班组派工
                    'sbCondition += String.Format(" AND (C.TeamDispatchNum =1  AND  A.CONTAINERID IS NOT NULL)") '批次在本工区存在班组派工
                    sbCondition += " AND C.TeamDispatchNum =1"
                Else '批次班组派工
                    sbCondition += "AND C.DISPATCHNUM=0 AND C.TeamDispatchNum =1"
                End If
            End If
        End If

        sb.AppendLine(sbCondition.ToString)
        sb.AppendLine(" AND C.CONTAINERTYPE='7'")
        sb.AppendLine(" GROUP BY C.PRODUCTID, C.PRODUCTNAME, C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID, C.WORKFLOWID, C.WorkflowStepSequence, C.SPECNO,")
        sb.AppendLine(" C.TOGETHERFLAG, C.CONTAINERTYPE,C.MFGORDERNAME,C.PLANQTY, C.PLANNEDSTARTDATE, C.PLANNEDCOMPLETIONDATE,")
        sb.AppendLine(" C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,c.containerno")
        sb.AppendLine(" UNION ")
        sb.AppendLine(strSelect)
        'sb.AppendLine("SUBSTR(C.CONTAINERNAME, LENGTH(C.PRODUCTNAME) + 2) TOGETHERFLAGNAME,")
        sb.AppendLine("C.QTY AS WIPQTY , C.CONTAINERID,C.CONTAINERNAME,C.WORKREPORTNUM,C.CONTAINERID AS FaContianerID,C.TeamDispatchID,C.TEAMNAME") 'add:Wawngjh 2017-7-12 增加C.TEAMNAME
        sb.AppendLine(" FROM CONTAINERCURRENTINFO C ")
        '燃烧分厂需要
        sb.AppendLine(" LEFT JOIN (" &
                      " SELECT  CTD.WORKCENTERID,CTD.TEAMID,CTD.CONTAINERID,CTD.CONTAINERTEAMDISPATCHID,CTD.WORKFLOWSTEPID FROM CONTAINERTEAMDISPATCH CTD " &
                    "  LEFT JOIN CONTAINER CO ON CO.CONTAINERID=CTD.CONTAINERID" &
                    "  WHERE   CTD.ISUSE=0")
        sb.AppendFormat(" AND CTD.WORKCENTERID='{0}'", QueryCondition("WorkCenterID"))
        sb.AppendLine(" ) A ON A.CONTAINERID  =C.CONTAINERID")
        '
        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE<>'7' ")

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
        paraPage.strSQL = sb.ToString

        sbCondition = String.Format("SELECT DISTINCT  C.* FROM CONTAINERCURRENTINFO C " &
             " LEFT JOIN (" &
            " SELECT  CTD.WORKCENTERID,CTD.TEAMID,CTD.CONTAINERID,CTD.CONTAINERTEAMDISPATCHID,CTD.WORKFLOWSTEPID FROM CONTAINERTEAMDISPATCH CTD " &
            " LEFT JOIN CONTAINER CO ON CO.CONTAINERID=CTD.CONTAINERID" &
            " WHERE   CTD.ISUSE=0 " &
            " AND CTD.WORKCENTERID='{0}'" &
            " ) A ON A.CONTAINERID  =C.CONTAINERID" &
            " {1}", QueryCondition("WorkCenterID"), sbCondition)

        Return DatabaseHelper.GetContainerForPage(paraPage, sbCondition)
    End Function

    ''' <summary>
    ''' '获取派工数据（新） create by tianFW 2018年6月27日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerStepDispatchDataNew(ByVal para As Dictionary(Of String, String)) As DTO.uMESProcessResult Implements uLMSDispatchIBusiness.GetContainerStepDispatchDataNew
        Dim strsql As String = ""
        'update:Wangjh 去掉  NVL(C.WORKFLOWROUTE,C.PRODUCTNAME)||'/'||REPLACE(DECODE(C.CONTAINERTYPE,7,C.TOGETHERFLAG,C.CONTAINERNAME),C.PRODUCTNAME||'/','') TOGETHERFLAGNAME, 增加ProductBatch
        strsql =
                <string>
                    SELECT NVL(C.WORKFLOWROUTE,C.PRODUCTNAME) PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE,C.TOGETHERFLAG, 
                    C.CONTAINERTYPE,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.PLANQTY,C.QTY,C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,
                    C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,DECODE(C.CONTAINERTYPE,7,C.TOGETHERFLAG,C.CONTAINERNAME) TOGETHERFLAGNAMES,
                    (nvl(c.Workflowroute,c.productname)||'/'||c.containerno) ProductBatch
                    FROM CONTAINERCURRENTINFO C 
                   
                </string>
        strsql += GetSearchStepDispatchStatus(para)
        If para.Keys.Contains("mfgOrderName") Then
            strsql += String.Format(" AND C.MFGORDERNAME LIKE '%{0}%'", para("mfgOrderName"))
        End If
        If para.ContainsKey("ContainerType") And para.ContainsKey("ScanContainerName") Then
            If para("ContainerType") = "7" Then
                strsql += String.Format(" AND C.TOGETHERFLAG='{0}'", para("ScanContainerName"))
            Else
                strsql += String.Format(" AND C.CONTAINERNAME='{0}'", para("ScanContainerName"))
            End If
        End If
        strsql = "SELECT K.PRODUCTNAME,K.PRODUCTID,K.WORKFLOWSTEPNAME,K.WORKFLOWSTEPID,K.SPECNO,K.WORKFLOWID,K.SEQUENCE,K.TOGETHERFLAG, " & vbCrLf & _
                "K.CONTAINERTYPE,K.CONTAINERTYPENAME,K.PLANQTY,K.PLANNEDSTARTDATE,K.PLANNEDCOMPLETIONDATE,K.WORKCENTERNAME,K.WORKCENTERID," & vbCrLf & _
                "K.FACTORYID,K.PRODUCTDESCRIPTION,K.PRODUCTREVISION,SUM(K.QTY) WIPQTY,K.MFGORDERNAME,K.ProductBatch,K.TOGETHERFLAGNAMES" & vbCrLf & _
                "FROM (" & strsql & ") K " & vbCrLf & _
                "GROUP BY  K.PRODUCTNAME,K.PRODUCTID,K.WORKFLOWSTEPNAME,K.WORKFLOWSTEPID,K.SPECNO,K.WORKFLOWID,K.SEQUENCE,K.TOGETHERFLAG," & vbCrLf & _
                "K.CONTAINERTYPE,K.MFGORDERNAME,K.PLANQTY,K.PLANNEDSTARTDATE,K.PLANNEDCOMPLETIONDATE,K.WORKCENTERNAME,K.WORKCENTERID,K.TOGETHERFLAGNAMES," & vbCrLf & _
                "K.FACTORYID,K.PRODUCTDESCRIPTION,K.PRODUCTREVISION,K.QTY,K.CONTAINERTYPENAME,K.ProductBatch ORDER BY K.PRODUCTNAME,K.TOGETHERFLAG"
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strsql
        Dim result As uMESProcessResult = DatabaseHelper.RunProcedureForPage(paraPage)
        Return result
    End Function

    ''' <summary>
    ''' 获取生产派工详细信息 create by tianFW 2018年6月27日 c.iscommissioning=1
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepDispatchDetail(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetStepDispatchDetail
        Dim strsql As String = String.Format("SELECT c.qty wipqty,c.workflowstepsequence sequence,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,c.*,CASE WHEN (c.isship = 1 AND c.originalfactoryid <> c.factoryid AND c.originalfactoryid='{0}') THEN '已转工'", para("FactoryID")) & vbCrLf & _
                                "WHEN (c.iscommissioning = 1) THEN '外委待调度'" & vbCrLf & _
                                "WHEN ((c.stepchecknum = 1 OR c.firstchecknum = 1) AND" & vbCrLf & _
                                "c.nonconformingnum = 1) THEN '待审理'" & vbCrLf & _
                                "WHEN ((c.stepchecknum = 1 OR c.firstchecknum = 1) AND" & vbCrLf & _
                                "(c.nonconformingnum <> 1 OR c.nonconformingnum IS NULL)) THEN '待检验'" & vbCrLf & _
                                "WHEN ((c.isdispatch = 1 AND c.qty >= csd_.disqty AND" & vbCrLf & _
                                "csd_.disqty > c.workreportnum AND c.stepchecknum = 0 AND" & vbCrLf & _
                                "(c.firstchecknum = 0 OR c.firstchecknum = 2)) OR" & vbCrLf & _
                                "(c.isdispatch = 1 AND c.qty < csd_.disqty AND" & vbCrLf & _
                                "c.qty > c.workreportnum AND c.stepchecknum = 0 AND" & vbCrLf & _
                                "(c.firstchecknum = 0 OR c.firstchecknum = 2))) THEN '待报工'" & vbCrLf & _
                                "WHEN ((c.isdispatch = 0 AND c.stepchecknum = 0) OR" & vbCrLf & _
                                "(c.isdispatch = 1 AND c.qty >= csd_.disqty AND" & vbCrLf & _
                                "c.qty > c.workreportnum AND c.workreportnum >= csd_.disqty AND" & vbCrLf & _
                                "c.stepchecknum = 0 AND (c.firstchecknum = 0 OR c.firstchecknum = 2))) THEN '待派工'" & vbCrLf & _
                             "END status "
        'add WHERE CSD.ISUSE = 0 by tianFW 2018年9月29日
        strsql += " FROM CONTAINERCURRENTINFO C " & vbCrLf & _
                       "LEFT JOIN(SELECT SUM(CSD.QTY) DISQTY,CSD.CONTAINERID, CSD.WORKFLOWSTEPID FROM CONTAINERSTEPDISPATCH CSD WHERE CSD.ISUSE = 0 " & vbCrLf & _
                       "GROUP BY CSD.CONTAINERID, CSD.WORKFLOWSTEPID ) CSD_ ON CSD_.CONTAINERID=C.CONTAINERID AND CSD_.WORKFLOWSTEPID=C.WORKFLOWSTEPID "
        strsql += GetSearchStepDispatchStatus(para)
        If para("ContainerType") = "7" Then
            strsql += String.Format(" AND C.TOGETHERFLAG='{0}'", para("TogatherFlag"))
        Else
            strsql += String.Format(" AND C.CONTAINERNAME='{0}'", para("TogatherFlag"))
        End If
        strsql += String.Format("  AND C.PRODUCTID='{0}' AND C.WORKFLOWSTEPID='{1}'", para("ProductID"), para("WorkflowStepID"))
        strsql += String.Format(" AND C.WORKCENTERID='{0}'", para("CuWorkCenterID"))
        If para.Keys.Contains("MfgOrderName") Then 'add by zhangrj 20220507 增加没有订单的批次也可以派工
            If Not String.IsNullOrWhiteSpace(para("MfgOrderName")) Then
                strsql += String.Format(" AND C.MFGORDERNAME = '{0}' ", para("MfgOrderName"))
            End If
        End If
        If para.ContainsKey("PlannedStartDate") Then 'add:Wangjh 20190219 增加判断
            If Not String.IsNullOrWhiteSpace(para("PlannedStartDate")) Then
                strsql += String.Format(" AND C.plannedstartdate = TO_DATE('{0}','yyyy-MM-dd')", para("PlannedStartDate"))
            Else
                strsql += " AND C.plannedstartdate IS NULL "
            End If
        End If
        If para.ContainsKey("PlannedCompletionDate") Then 'add:Wangjh 20190219 增加判断
            If Not String.IsNullOrWhiteSpace(para("PlannedCompletionDate")) Then
                strsql += String.Format(" AND C.plannedcompletiondate = TO_DATE('{0}','yyyy-MM-dd')", para("PlannedCompletionDate"))
            Else
                strsql += " AND C.plannedcompletiondate IS NULL "
            End If
        End If

        strsql += " ORDER BY C.CONTAINERNAME"
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 获取生产派工状态查询处理 create by tianFw 2018年6月27日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSearchStepDispatchStatus(ByVal para As Dictionary(Of String, String)) As String Implements uLMSDispatchIBusiness.GetSearchStepDispatchStatus
        Dim strsql As String = ""
        If para.Keys.Contains("StepDispatch") Then '区分是工序工区工序派工或者主制工区工序派工 
            Dim valuestr As String = ""
            'If para("Status") = "1" Then
            '    valuestr =
            '   <string>
            '        INNER JOIN(
            '        SELECT ctd.containerid,COUNT(ctd.containerteamdispatchid) count_ctd FROM containerteamdispatch ctd 
            '        LEFT JOIN(
            '        SELECT ds.containerid,ds.workflowstepid,SUM(ds.qty) qty FROM containerstepdispatch ds
            '        WHERE ds.isuse = 0 GROUP BY ds.containerid,ds.workflowstepid
            '        ) dss ON dss.containerid = ctd.containerid AND dss.workflowstepid = ctd.workflowstepid
            '        LEFT JOIN Workflowstep wss ON wss.workflowstepid = ctd.workflowstepid
            '        LEFT JOIN containercurrentinfo cio ON cio.containerid = ctd.containerid
            '        WHERE cio.qty > nvl(dss.qty,0) AND ctd.isuse = 0 AND cio.containerstatus=1 AND 
            '        wss.sequence>=cio.workflowstepsequence 
            '    </string>
            '    If para.ContainsKey("TeamID") Then
            '        valuestr += String.Format(" AND ctd.teamid='{0}'  ", para("TeamID"))
            '    End If
            '    valuestr += (" GROUP BY ctd.containerid) ctdd ON ctdd.containerid = c.containerid ")
            'End If
            'strsql += valuestr
            strsql += GetCondition_SetpDispatch(para)
        Else
            strsql += GetCondition(para)
            If para.ContainsKey("WorkCenterID") Then
                'add:Wangjh 2017-10-19  OR (C.FROMWORKCENTERID='{0}' AND C.ISSHIP=1)
                strsql += String.Format("  AND (C.WORKCENTERID='{0}' OR (C.TOWORKCENTERID='{0}' AND C.WORKCENTERDISPATCHNUM=0) OR (C.FROMWORKCENTERID='{0}' AND C.ISSHIP=1) or c.STEPWORKCENTERIDS  LIKE '%{0}%' ) ", para("WorkCenterID"), para("WorkCenterID"))
            End If
        End If
        If para.Keys.Contains("IsChangeDispatch") Then
            strsql += " AND C.DISPATCHNUM=1 " 'tianFW 2017-06-03修改
        Else
            If para.Keys.Contains("Status") Then
                If para("Status") = "1" Then '待派工,对于协作工区需要工区长接收
                    If para.Keys.Contains("StepDispatch") Then
                        If para.ContainsKey("TeamID") Then
                            strsql += String.Format(<string> AND EXISTS (     
                        SELECT 1  FROM containerteamdispatch ctd 
                        LEFT JOIN workflowstep ws ON ws.workflowstepid=ctd.workflowstepid
                        WHERE ctd.containerid=c.containerid AND ctd.teamid='{0}' AND ctd.isuse=0 AND ws.workflowid=c.workflowid AND ws.sequence>=c.workflowstepsequence) </string>.Value, para("TeamID"))
                        End If
                    End If
                    strsql += " AND ((C.ISDISPATCH=0 AND C.STEPCHECKNUM=0)" 'Update:Wangjh 2017-6-16  ’删除AND C.DISPATCHNUM=0 tianFW 2017-07-14 ,UPDATE:Wangjh 2017-7-21 删除AND DECODE(C.WORKCENTERDISPATCHID,NULL,'1',CWD.RECVEMPLOYEEID) IS NOT NULL
                    'strsql += " OR (C.ISDISPATCH=1 AND C.QTY>=CSD_.DISQTY AND C.QTY>C.WORKREPORTNUM AND C.WORKREPORTNUM>=CSD_.DISQTY AND C.STEPCHECKNUM=0 AND (C.FIRSTCHECKNUM=0 OR C.FIRSTCHECKNUM=2))) "
                    strsql += <string> or ( C.ISDISPATCH=1 and C.QTY>C.WORKREPORTNUM and C.STEPCHECKNUM=0 AND (C.FIRSTCHECKNUM=0 OR C.FIRSTCHECKNUM=2)
                        and exists (
                            SELECT SUM(CSD.QTY) DISQTY,CSD.CONTAINERID, CSD.WORKFLOWSTEPID FROM CONTAINERSTEPDISPATCH CSD                    
                            WHERE CSD.ISUSE=0 and csd.containerid=c.containerid and csd.workflowstepid=c.workflowstepid                    
                            GROUP BY CSD.CONTAINERID, CSD.WORKFLOWSTEPID 
                            having c.qty>=sum(csd.qty) and c.workreportnum>=sum(csd.qty)
                        )
                    )</string>.Value
                    strsql += " ) "
                    strsql += " AND NOT (C.STEPCHECKNUM=1 OR C.FIRSTCHECKNUM=1)"
                    strsql += String.Format(" AND NOT (C.ISSHIP=1 AND C.ORIGINALFACTORYID <> C.FACTORYID AND C.ORIGINALFACTORYID='{0}')", para("FactoryID"))
                    strsql += " AND NOT (C.iscommissioning=1)"
                    '如果C.QTY<CSD_.DISQTY那除了其他状态之外就是待报工状态，相当于已经派完了
                ElseIf para("Status") = "2" Then '已派工
                    strsql += " AND C.ISDISPATCH=1" '修改为当前序有派工C.DISPATCHNUM=1 -> C.ISDISPATCH=1 tianfw
                ElseIf para("Status") = "3" Then '待报工
                    'strsql += " AND ((C.ISDISPATCH=1 AND C.QTY>=CSD_.DISQTY AND CSD_.DISQTY>C.WORKREPORTNUM AND C.STEPCHECKNUM=0 AND (C.FIRSTCHECKNUM=0 OR C.FIRSTCHECKNUM=2)) "
                    'strsql += " OR (C.ISDISPATCH=1 AND C.QTY<CSD_.DISQTY AND C.QTY>C.WORKREPORTNUM AND C.STEPCHECKNUM=0 AND (C.FIRSTCHECKNUM=0 OR C.FIRSTCHECKNUM=2)))"
                    strsql += <string> and (
                                C.ISDISPATCH=1 and C.STEPCHECKNUM=0 AND (C.FIRSTCHECKNUM=0 OR C.FIRSTCHECKNUM=2)
                                and exists (
                                     SELECT SUM(CSD.QTY) DISQTY,CSD.CONTAINERID, CSD.WORKFLOWSTEPID FROM CONTAINERSTEPDISPATCH CSD                    
                                    WHERE CSD.ISUSE=0 and csd.containerid=c.containerid and csd.workflowstepid=c.workflowstepid                    
                                    GROUP BY CSD.CONTAINERID, CSD.WORKFLOWSTEPID 
                                    having sum(csd.qty)>c.workreportnum
                                )
                             ) </string>.Value
                ElseIf para("Status") = "4" Then '待检验
                    strsql += "  AND (C.STEPCHECKNUM=1 OR C.FIRSTCHECKNUM=1) AND (C.NONCONFORMINGNUM<>1 OR C.NONCONFORMINGNUM IS NULL)"
                ElseIf para("Status") = "5" Then '待审理
                    strsql += "  AND (C.STEPCHECKNUM=1 OR C.FIRSTCHECKNUM=1) AND C.NONCONFORMINGNUM=1"
                ElseIf para("Status") = "6" Then '已转工
                    strsql += String.Format(" AND C.ISSHIP=1 AND C.ORIGINALFACTORYID <> C.FACTORYID AND C.ORIGINALFACTORYID='{0}'", para("FactoryID"))
                End If
                If para("Status") = 3 Or para("Status") = 4 Or para("Status") = 5 Then
                    strsql += String.Format(" AND C.FACTORYID='{0}' ", para("FactoryID"))
                End If
            End If
        End If
        strsql += " AND C.SPLITNUM = 0 AND C.ISINSTORE=0 AND C.CONTAINERSTATUS=1 "
        GetSearchStepDispatchStatus = strsql
    End Function


    ''' <summary>
    ''' 获取生产准备数据
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks>REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME, modify by tianFW 2018年5月17日 单件单卡取C.TOGETHERFLAG 数量批取C.PRODUCTNAME</remarks>
    Public Function GetContainerPreparationData(ByVal QueryCondition As System.Collections.Generic.Dictionary(Of String, String)) As DTO.uMESProcessResult Implements IBusiness.uLMSDispatchIBusiness.GetContainerPreparationData
        Dim strSelect As String =
            <string>
             SELECT C.PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE,C.TOGETHERFLAG, 
             C.CONTAINERTYPE,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.PLANQTY,
             C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,(c.productname||'/'||c.containerno) ProductBatch, 
             
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strSelect)
        ' sb.AppendLine("REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME,")
        sb.AppendLine("SUM(C.QTY) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,NULL WORKREPORTNUM,'' FaContianerID,'' TEAMID,am.toolstatus")
        sb.AppendLine(",null DispartDate,null DispartFinishDate,w2.WORKFLOWNAME||':'||w.WORKFLOWREVISION WORKFLOWNAMEVERSION")
        sb.AppendLine(" FROM CONTAINERCURRENTINFO C  ")

        sb.AppendLine(" LEFT JOIN WORKFLOW w ON w.WORKFLOWID =c.WORKFLOWID") 'add by longb 20221-26
        sb.AppendLine(" LEFT JOIN WORKFLOWBASE w2 ON w2.WORKFLOWBASEID =w.WORKFLOWBASEID") 'add by longb 20221-26

        'add YangJiang 20180417
        If QueryCondition.Keys.Contains("Status") Then
            If QueryCondition("Status") = "1" Then
                sb.AppendLine(" LEFT JOIN arrangements am ON c.containerid=am.containerid AND c.workflowstepid=am.workflowstepid")
            Else
                sb.AppendLine(" LEFT JOIN arrangementsContainer ac ON ac.containerid=c.containerid")
                sb.AppendLine(" LEFT JOIN arrangements am ON am.arrangementsid=ac.arrangementsid ")
            End If
        End If



        'add YangJiang 20180411
        If QueryCondition.Keys.Contains("WorkerID") Then
            'add and csd.isuse = 0 by tianFW 2018年9月29日
            If QueryCondition("FactoryID") = "0004e28000000dc0" Then
                sb.AppendFormat(" INNER JOIN (SELECT csd.containerid, max(csd.workflowstepid) workflowstepid" &
                       " FROM containerstepdispatch csd " &
                       " left join containerstepdispatchdetail csdd on csdd.containerstepdispatchid = csd.containerstepdispatchid " &
                       " WHERE NVL(csd.workerid,csdd.workerid)='{0}' and csd.isuse = 0  GROUP BY csd.containerid) B" &
                       " ON B.CONTAINERID=c.containerid AND B.workflowstepid>C.workflowstepid ", QueryCondition("WorkerID"))
            Else
                sb.AppendFormat(" INNER JOIN (SELECT csd.containerid, max(csd.workflowstepid) workflowstepid" &
                            " FROM containerstepdispatch csd " &
                            " WHERE csd.workerid='{0}' and csd.isuse = 0  GROUP BY csd.containerid) B" &
                            " ON B.CONTAINERID=c.containerid AND B.workflowstepid>C.workflowstepid ", QueryCondition("WorkerID"))
            End If

        End If


        Dim sbCondition As String = GetCondition(QueryCondition)
        sbCondition += " AND C.CONTAINERSTATUS=1 " 'add tianFW 2017/11/02
        sbCondition += " AND (C.DISPATCHNUM=1 or c.stepchecknum=1) AND C.ISINSTORE=0 " 'add YangJiang 2018/4/18
        sb.AppendLine(sbCondition)
        'add YangJiang 20180417
        If QueryCondition.Keys.Contains("Status") Then
            If QueryCondition("Status") = "1" Then
                sb.AppendLine(" AND AM.CONTAINERID IS NULL")
            ElseIf QueryCondition("Status") = "2" Then
                sb.AppendLine(" AND AM.TOOLSTATUS='待准备'")
            ElseIf QueryCondition("Status") = "3" Then
                sb.AppendLine(" AND AM.TOOLSTATUS IS NOT NULL AND AM.TOOLSTATUS<>'待准备' ")
            End If
        End If


        sb.AppendLine(" AND C.CONTAINERTYPE='7'")
        sb.AppendLine(" GROUP BY C.PRODUCTID, C.PRODUCTNAME, C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID, C.WORKFLOWID, C.WORKFLOWSTEPSEQUENCE, C.SPECNO,") '将 C.SEQUENCE更换为C.WORKFLOWSTEPSEQUENCE tianFW 2017-06-03
        sb.AppendLine(" C.TOGETHERFLAG, C.CONTAINERTYPE,C.MFGORDERNAME,C.PLANQTY, C.PLANNEDSTARTDATE, C.PLANNEDCOMPLETIONDATE,")
        sb.AppendLine(" C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,am.toolstatus,c.containerno,w2.WORKFLOWNAME||':'||w.WORKFLOWREVISION ")


        sb.AppendLine(" UNION ")
        sb.AppendLine(strSelect)
        ' sb.AppendLine("SUBSTR(C.CONTAINERNAME, LENGTH(C.PRODUCTNAME) + 2) TOGETHERFLAGNAME,")
        sb.AppendLine("C.QTY AS WIPQTY , C.CONTAINERID,C.CONTAINERNAME,C.WORKREPORTNUM,C.CONTAINERID AS FaContianerID,C.TEAMID,am.toolstatus ")

        sb.AppendLine(",min(A.plannedstartdate) DispartDate,max(A.plannedfinishdate) DispartFinishDate,w2.WORKFLOWNAME||':'||w.WORKFLOWREVISION WORKFLOWNAMEVERSION") 'Modify YangJiang 20180708min(A.plannedstartdate),max(A.plannedfinishdate)
        sb.AppendLine(" FROM CONTAINERCURRENTINFO C  ")
        sb.AppendLine(" LEFT JOIN WORKFLOW w ON w.WORKFLOWID =c.WORKFLOWID") 'add by longb 20221-26
        sb.AppendLine(" LEFT JOIN WORKFLOWBASE w2 ON w2.WORKFLOWBASEID =w.WORKFLOWBASEID") 'add by longb 20221-26

        sb.AppendLine("  LEFT JOIN (SELECT csd.containerid,MIN(csd.createdate) plannedstartdate,MIN(csd.plannedfinishdate) plannedfinishdate,csd.workflowstepid  FROM containerstepdispatch csd ")
        sb.AppendLine(" where csd.isuse = 0 GROUP BY csd.containerid,csd.workflowstepid ) A ON A.containerid=c.containerid  ") 'add YangJiang 20180515 获取派工时间和要求完成时间


        'add YangJiang 20180417
        If QueryCondition.Keys.Contains("Status") Then
            If QueryCondition("Status") = "1" Then
                sb.AppendLine(" LEFT JOIN arrangements am ON am.containerid=a.containerid AND am.workflowstepid=a.workflowstepid")
            Else
                sb.AppendLine(" LEFT JOIN arrangements am ON c.containerid=am.containerid ")
            End If
        End If

        'add YangJiang 20180411
        If QueryCondition.Keys.Contains("WorkerID") Then
            'add zhunan 2021.5.26 增加判断逻辑 燃烧分厂派给工人为空时从派工明细表查寻 NVL(csd.workerid,csdd.workerid)='{0}'
            If QueryCondition("FactoryID") = "0004e28000000dc0" Then
                sb.AppendFormat(" INNER JOIN (SELECT csd.containerid, max(csd.workflowstepid) workflowstepid" &
                            " FROM containerstepdispatch csd " &
                            " left join containerstepdispatchdetail csdd on csdd.containerstepdispatchid = csd.containerstepdispatchid " &
                            " WHERE NVL(csd.workerid,csdd.workerid)='{0}' and csd.isuse = 0  GROUP BY csd.containerid) B" &
                            " ON B.CONTAINERID=c.containerid AND B.workflowstepid>C.workflowstepid ", QueryCondition("WorkerID"))
            Else
                sb.AppendFormat(" INNER JOIN (SELECT csd.containerid, max(csd.workflowstepid) workflowstepid" &
                            " FROM containerstepdispatch csd " &
                            " WHERE csd.workerid='{0}' and csd.isuse = 0  GROUP BY csd.containerid) B" &
                            " ON B.CONTAINERID=c.containerid AND B.workflowstepid>C.workflowstepid ", QueryCondition("WorkerID"))
            End If
        End If
        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE<>'7' ")
        'add YangJiang 20180417
        If QueryCondition.Keys.Contains("Status") Then
            If QueryCondition("Status") = "1" Then
                sb.AppendLine("  AND Am.workflowstepid IS NULL")
            ElseIf QueryCondition("Status") = "2" Then
                sb.AppendLine(" AND AM.TOOLSTATUS='待准备'")
            ElseIf QueryCondition("Status") = "3" Then
                sb.AppendLine(" AND AM.TOOLSTATUS IS NOT NULL AND AM.TOOLSTATUS<>'待准备' ")
            End If
        End If

        'add YangJiang 20180708
        sb.AppendLine("GROUP BY C.PRODUCTID, C.PRODUCTNAME, C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID, C.WORKFLOWID, C.WORKFLOWSTEPSEQUENCE, C.SPECNO,C.CONTAINERNAME,")
        sb.AppendLine("C.WORKREPORTNUM,C.TOGETHERFLAG, C.CONTAINERTYPE,C.MFGORDERNAME,C.PLANQTY, C.PLANNEDSTARTDATE, C.PLANNEDCOMPLETIONDATE,C.QTY, C.CONTAINERID,C.TEAMID,")
        sb.AppendLine(" C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,am.toolstatus ,c.containerno,w2.WORKFLOWNAME||':'||w.WORKFLOWREVISION")


        Dim paraPage As New uMESPagingDataDTO
        If QueryCondition.Keys.Contains("CurrentPageIndex") Then
            paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex"))
        End If
        If QueryCondition.Keys.Contains("PageSize") Then
            paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
        End If

        paraPage.strSQL = sb.ToString

        'add YangJiang 20180411
        If QueryCondition.Keys.Contains("WorkerID") Then
            'add zhunan 2021.5.26 增加判断逻辑 燃烧分厂派给工人为空时从派工明细表查寻 NVL(csd.workerid,csdd.workerid)='{0}'
            If QueryCondition("FactoryID") = "0004e28000000dc0" Then
                sbCondition = String.Format("SELECT C.*,A.plannedstartdate DispartDate,A.plannedfinishdate DispartFinishDate FROM CONTAINERCURRENTINFO C " &
                                            "LEFT JOIN (SELECT csd.containerid,MIN(csd.createdate) plannedstartdate,MIN(csd.plannedfinishdate) plannedfinishdate " &
                                            "FROM containerstepdispatch csd GROUP BY csd.containerid) A ON A.containerid=c.containerid " &
                                            " LEFT JOIN (SELECT csd.containerid,max(csd.workflowstepid) workflowstepid" &
                                            " FROM containerstepdispatch csd  " &
                                            " left join containerstepdispatchdetail csdd on csdd.containerstepdispatchid = csd.containerstepdispatchid " &
                                            " WHERE NVL(csd.workerid,csdd.workerid)='{0}' and csd.isuse = 0  GROUP BY csd.containerid) B" &
                                            " ON B.CONTAINERID=c.containerid {1} AND B.workflowstepid>=C.workflowstepid", QueryCondition("WorkerID"), sbCondition)
            Else
                'add and csd.isuse = 0 by tianFW 2018年9月29日
                sbCondition = String.Format("SELECT C.*,A.plannedstartdate DispartDate,A.plannedfinishdate DispartFinishDate FROM CONTAINERCURRENTINFO C " &
                                            "LEFT JOIN (SELECT csd.containerid,MIN(csd.createdate) plannedstartdate,MIN(csd.plannedfinishdate) plannedfinishdate " &
                                            "FROM containerstepdispatch csd GROUP BY csd.containerid) A ON A.containerid=c.containerid " &
                                            " LEFT JOIN (SELECT csd.containerid,max(csd.workflowstepid) workflowstepid" &
                                            " FROM containerstepdispatch csd  " &
                                            " WHERE csd.workerid='{0}' and csd.isuse = 0  GROUP BY csd.containerid) B" &
                                            " ON B.CONTAINERID=c.containerid {1} AND B.workflowstepid>=C.workflowstepid", QueryCondition("WorkerID"), sbCondition)

            End If
        Else
            sbCondition = String.Format("SELECT C.*,A.plannedstartdate DispartDate,A.plannedfinishdate DispartFinishDate FROM CONTAINERCURRENTINFO C " &
                                        "LEFT JOIN (SELECT csd.containerid,MIN(csd.createdate) plannedstartdate,MIN(csd.plannedfinishdate) plannedfinishdate " &
                                        "FROM containerstepdispatch csd WHERE csd.isuse = 0 GROUP BY csd.containerid) A ON A.containerid=c.containerid {0}", sbCondition)
        End If

        Return DatabaseHelper.GetContainerForPage(paraPage, sbCondition)

    End Function


    Public Function GetContainerPreparationDataNew(ByVal QueryCondition As System.Collections.Generic.Dictionary(Of String, String)) As DTO.uMESProcessResult Implements IBusiness.uLMSDispatchIBusiness.GetContainerPreparationDataNew
        Dim strSelect As String =
           <string>
             SELECT C.PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE,C.TOGETHERFLAG, 
             C.CONTAINERTYPE,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.PLANQTY,
             C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,(c.productname||'/'||c.containerno) ProductBatch,
            </string>.Value
        Dim sb As New StringBuilder
        sb.AppendLine(strSelect)
        sb.AppendLine("SUM(C.QTY) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,NULL WORKREPORTNUM,'' FaContianerID,'' TEAMID,am.toolstatus")
        sb.AppendLine(",null DispartDate,null DispartFinishDate,w2.WORKFLOWNAME||':'||w.WORKFLOWREVISION WORKFLOWNAMEVERSION")
        sb.AppendLine(" FROM CONTAINERCURRENTINFO C  ")

        sb.AppendLine(" LEFT JOIN WORKFLOW w ON w.WORKFLOWID =c.WORKFLOWID") 'add by longb 20221-26
        sb.AppendLine(" left join workflowstep ws on ws.workflowid=w.workflowid")
        sb.AppendLine(" LEFT JOIN WORKFLOWBASE w2 ON w2.WORKFLOWBASEID =w.WORKFLOWBASEID") 'add by longb 20221-26

        'add YangJiang 20180417
        If QueryCondition.Keys.Contains("Status") Then
            sb.AppendLine(" LEFT JOIN arrangements am ON c.containerid=am.containerid AND c.workflowid=am.workflowid ")

        End If

        Dim sbCondition As String = GetCondition(QueryCondition)

        If QueryCondition.Keys.Contains("WorkerID") Then
            sbCondition += String.Format(<string> AND exists (select 1 from containerstepdispatch csd 
 left join containerstepdispatchdetail csdd on csdd.containerstepdispatchid=csd.containerstepdispatchid
  where csd.containerid=c.containerid and csd.workflowstepid=ws.workflowstepid and csd.isuse=0 and (csd.workerid='{0}' or csdd.workerid='{0}'))</string>.Value, QueryCondition("WorkerID"))
        End If

        sbCondition += " AND C.CONTAINERSTATUS=1 " 'add tianFW 2017/11/02
        sbCondition += " AND (C.DISPATCHNUM=1 or c.stepchecknum=1) AND C.ISINSTORE=0 " 'add YangJiang 2018/4/18
        sb.AppendLine(sbCondition)
        'add YangJiang 20180417
        If QueryCondition.Keys.Contains("Status") Then
            If QueryCondition("Status") = "1" Then
                sb.AppendLine(" AND AM.CONTAINERID IS NULL")
            ElseIf QueryCondition("Status") = "2" Then
                sb.AppendLine(" AND AM.TOOLSTATUS='待准备'")
            ElseIf QueryCondition("Status") = "3" Then
                sb.AppendLine(" AND AM.TOOLSTATUS IS NOT NULL AND AM.TOOLSTATUS<>'待准备' ")
            End If
        End If


        sb.AppendLine(" AND C.CONTAINERTYPE='7'")
        sb.AppendLine(" GROUP BY C.PRODUCTID, C.PRODUCTNAME, C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID, C.WORKFLOWID, C.WORKFLOWSTEPSEQUENCE, C.SPECNO,") '将 C.SEQUENCE更换为C.WORKFLOWSTEPSEQUENCE tianFW 2017-06-03
        sb.AppendLine(" C.TOGETHERFLAG, C.CONTAINERTYPE,C.MFGORDERNAME,C.PLANQTY, C.PLANNEDSTARTDATE, C.PLANNEDCOMPLETIONDATE,")
        sb.AppendLine(" C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,am.toolstatus,c.containerno,w2.WORKFLOWNAME||':'||w.WORKFLOWREVISION ")


        sb.AppendLine(" UNION ")
        sb.AppendLine(strSelect)
        ' sb.AppendLine("SUBSTR(C.CONTAINERNAME, LENGTH(C.PRODUCTNAME) + 2) TOGETHERFLAGNAME,")
        sb.AppendLine("C.QTY AS WIPQTY , C.CONTAINERID,C.CONTAINERNAME,C.WORKREPORTNUM,C.CONTAINERID AS FaContianerID,C.TEAMID,am.toolstatus ")

        sb.AppendLine(",min(csd.plannedstartdate) DispartDate,max(csd.plannedfinishdate) DispartFinishDate,w2.WORKFLOWNAME||':'||w.WORKFLOWREVISION WORKFLOWNAMEVERSION") 'Modify YangJiang 20180708min(A.plannedstartdate),max(A.plannedfinishdate)
        sb.AppendLine(" FROM CONTAINERCURRENTINFO C  ")
        sb.AppendLine(" LEFT JOIN WORKFLOW w ON w.WORKFLOWID =c.WORKFLOWID") 'add by longb 20221-26
        sb.AppendLine(" left join workflowstep ws on ws.workflowid=w.workflowid")
        sb.AppendLine(" LEFT JOIN WORKFLOWBASE w2 ON w2.WORKFLOWBASEID =w.WORKFLOWBASEID") 'add by longb 20221-26
        sb.AppendLine(" left join containerstepdispatch csd on csd.containerid=c.containerid and csd.workflowstepid=ws.workflowstepid and csd.isuse=0 ")
        'add YangJiang 20180417
        If QueryCondition.Keys.Contains("Status") Then
            sb.AppendLine(" LEFT JOIN arrangements am ON am.containerid=c.containerid AND am.workflowstepid=ws.workflowstepid")
        End If

        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE<>'7' ")
        'add YangJiang 20180417
        If QueryCondition.Keys.Contains("Status") Then
            If QueryCondition("Status") = "1" Then
                sb.AppendLine("  AND Am.workflowstepid IS NULL")
            ElseIf QueryCondition("Status") = "2" Then
                sb.AppendLine(" AND AM.TOOLSTATUS='待准备'")
            ElseIf QueryCondition("Status") = "3" Then
                sb.AppendLine(" AND AM.TOOLSTATUS IS NOT NULL AND AM.TOOLSTATUS<>'待准备' ")
            End If
        End If

        'add YangJiang 20180708
        sb.AppendLine("GROUP BY C.PRODUCTID, C.PRODUCTNAME, C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID, C.WORKFLOWID, C.WORKFLOWSTEPSEQUENCE, C.SPECNO,C.CONTAINERNAME,")
        sb.AppendLine("C.WORKREPORTNUM,C.TOGETHERFLAG, C.CONTAINERTYPE,C.MFGORDERNAME,C.PLANQTY, C.PLANNEDSTARTDATE, C.PLANNEDCOMPLETIONDATE,C.QTY, C.CONTAINERID,C.TEAMID,")
        sb.AppendLine(" C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,am.toolstatus ,c.containerno,w2.WORKFLOWNAME||':'||w.WORKFLOWREVISION")


        Dim paraPage As New uMESPagingDataDTO
        If QueryCondition.Keys.Contains("CurrentPageIndex") Then
            paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex"))
        End If
        If QueryCondition.Keys.Contains("PageSize") Then
            paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
        End If

        paraPage.strSQL = sb.ToString

        'add YangJiang 20180411
        sbCondition = String.Format("SELECT distinct C.*,A.plannedstartdate DispartDate,A.plannedfinishdate DispartFinishDate FROM CONTAINERCURRENTINFO C " &
                                    " left join workflowstep ws on ws.workflowid=c.workflowid " &
                                    "LEFT JOIN (SELECT csd.containerid,MIN(csd.createdate) plannedstartdate,MIN(csd.plannedfinishdate) plannedfinishdate " &
                                    "FROM containerstepdispatch csd WHERE csd.isuse = 0 GROUP BY csd.containerid) A ON A.containerid=c.containerid {0}", sbCondition)


        Return DatabaseHelper.GetContainerForPage(paraPage, sbCondition)
    End Function

    ''' <summary>
    ''' 创建批次查询数据条件 add tianFW
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetCondition_SetpDispatch(ByVal para As Dictionary(Of String, String)) As String
        Dim sbCondition As New StringBuilder
        sbCondition.AppendFormat(" WHERE (C.ORIGINALFACTORYID='{0}' OR (C.FACTORYID='{0}' AND C.ORIGINALFACTORYID!='{0}' and c.shipnum>4 ) )", para("FactoryID"))
        If para.Keys.Contains("TeamID") Then
            sbCondition.AppendFormat(" AND (C.STEPTEAMIDS LIKE '%{0}%' or c.teamid='{0}' )", para("TeamID"))
        End If
        If para.Keys.Contains("ProductName") Then
            sbCondition.AppendFormat(" AND C.PRODUCTNAME LIKE'%{0}%'", para("ProductName"))
        End If
        If para.Keys.Contains("StepNo") Then
            sbCondition.AppendFormat(" AND C.SPECNO LIKE'%{0}%'", para("StepNo"))
        End If
        If para.Keys.Contains("ContainerName") Then
            sbCondition.AppendFormat(" AND C.CONTAINERNAME LIKE '%{0}%'", para("ContainerName"))
        End If
        If para.Keys.Contains("MfgOrderName") Then
            If Not String.IsNullOrWhiteSpace(para("MfgOrderName")) Then
                sbCondition.AppendFormat(" AND C.MfgOrderName LIKE '%{0}%'", para("MfgOrderName"))
            End If
        End If
        If para.ContainsKey("WorkCenterID") Then
            sbCondition.AppendFormat(" AND (C.STEPWORKCENTERIDS  LIKE '%{0}%' or c.WorkCenterID='{0}' or c.toWorkCenterID='{0}') ", para("WorkCenterID"))
        End If
        GetCondition_SetpDispatch = sbCondition.ToString
    End Function

    ''' <summary>
    ''' 创建批次查询数据条件
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetCondition(ByVal para As Dictionary(Of String, String)) As String
        Dim sbCondition As New StringBuilder
        sbCondition.AppendFormat(" WHERE (C.ORIGINALFACTORYID='{0}' OR (C.FACTORYID='{0}' AND C.ORIGINALFACTORYID!='{0}' and c.shipnum>4 ) )", para("FactoryID"))
        If para.Keys.Contains("TeamID") Then
            sbCondition.AppendFormat(" AND C.TEAMID='{0}' ", para("TeamID"))
        End If
        If para.Keys.Contains("ProductName") Then
            sbCondition.AppendFormat(" AND C.PRODUCTNAME LIKE'%{0}%'", para("ProductName").Trim.ToUpper)
        End If
        If para.Keys.Contains("StepNo") Then
            sbCondition.AppendFormat(" AND C.SPECNO LIKE'%{0}%'", para("StepNo"))
        End If
        If para.Keys.Contains("ContainerName") Then
            sbCondition.AppendFormat(" AND C.CONTAINERNAME LIKE '%{0}%'", para("ContainerName"))
        End If
        If para.Keys.Contains("MfgOrderName") Then 'add:wangjh 2017-8-8
            If Not String.IsNullOrWhiteSpace(para("MfgOrderName")) Then
                sbCondition.AppendFormat(" AND C.MfgOrderName LIKE '%{0}%'", para("MfgOrderName"))
            End If
        End If
        GetCondition = sbCondition.ToString
    End Function

    ''' <summary>
    ''' 获取工序工具列表数据
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetStepToolsData(ByVal para As Dictionary(Of String, String), ByVal type As String, Optional ByVal isCludeStepInfo As Boolean = False) As DataTable Implements uLMSDispatchIBusiness.GetStepToolsData 'modify tianFW 2017/09/25 将stepID和containerID用dictionary传过来
        'modify tianFW 
        Dim stepID As String = para("stepID")
        Dim containerID As String = para("containerID")
        Dim value As String = ""
        If para.Keys.Contains("Type") Then
            value = para("Type")
        End If
        Dim ToolNo As String = ""
        If para.Keys.Contains("ToolNo") Then
            ToolNo = para("ToolNo")
        End If
        '查询刀具
        Dim strSQL As String = <string>  SELECT distinct  C.CONTAINERNAME,WFS.WORKFLOWSTEPNAME,WFST.WORKFLOWSTEPID,null stepinfoid,WFST.QTY, WFST.SEQUENCE,
CT.CUTTOOLID TOOLID, CT.CUTTOOLNAME TOOLNAME,'cuttool' TYPE ,'刀具' TypeName,
CT.DESCRIPTION,RT.RESOURCETYPENAME, A.TOOLSTATUS,CT.drawingno,a.arrangementsid 
FROM WORKFLOWSTEPTOOLS WFST
  LEFT JOIN WORKFLOWSTEP WFS  ON WFS.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID
  LEFT JOIN CUTTOOL CT  ON CT.CUTTOOLID = WFST.TOOLS
  LEFT JOIN RESOURCETYPE RT  ON RT.RESOURCETYPEID = CT.RESOURCETYPEID

  LEFT JOIN ARRANGEMENTS A  ON A.CONTAINERID IN ({0}) and A.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID 
  AND A.TOOLNO = CT.CUTTOOLNAME and  a.stepinfoid is null
  
  LEFT JOIN CONTAINER C  ON C.CONTAINERID = A.CONTAINERID
   WHERE wfst.WORKFLOWSTEPID in ({1})  and CT.CUTTOOLID != ' '   </string>.Value

        strSQL = String.Format(strSQL, containerID, stepID)
        If Not String.IsNullOrWhiteSpace(ToolNo) Then
            strSQL += String.Format(" and ct.CUTTOOLname='{0}' ", ToolNo)
        End If
        If isCludeStepInfo Then
            strSQL += <string> union all
            SELECT distinct  C.CONTAINERNAME,WFS.WORKFLOWSTEPNAME,wfs.WORKFLOWSTEPID,wfst.stepinfoid,WFST.QTY, WFST.SEQUENCE,
            CT.CUTTOOLID TOOLID, CT.CUTTOOLNAME TOOLNAME,'cuttool' TYPE ,'刀具' TypeName,
            CT.DESCRIPTION,RT.RESOURCETYPENAME, A.TOOLSTATUS,CT.drawingno,a.arrangementsid 
            FROM stepinfotools WFST
            left join stepinfo si on si.stepinfoid=wfst.stepinfoid
            LEFT JOIN WORKFLOWSTEP WFS  ON WFS.WORKFLOWSTEPID = si.WORKFLOWSTEPID
            LEFT JOIN CUTTOOL CT  ON CT.CUTTOOLID = WFST.TOOLS
            LEFT JOIN RESOURCETYPE RT  ON RT.RESOURCETYPEID = CT.RESOURCETYPEID
            LEFT JOIN ARRANGEMENTS A  ON A.CONTAINERID IN ({0}) and A.WORKFLOWSTEPID = wfs.WORKFLOWSTEPID 
            AND A.TOOLNO = CT.CUTTOOLNAME and  a.stepinfoid=wfst.stepinfoid
            LEFT JOIN CONTAINER C  ON C.CONTAINERID = A.CONTAINERID
            WHERE wfs.WORKFLOWSTEPID in ({1})  and CT.CUTTOOLID != ' ' </string>.Value
            strSQL = String.Format(strSQL, containerID, stepID)
            If Not String.IsNullOrWhiteSpace(ToolNo) Then
                strSQL += String.Format(" and ct.CUTTOOLname='{0}' ", ToolNo)
            End If
        End If


        Dim cuttooldt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)

        '查询量具
        strSQL = <string> SELECT distinct  C.CONTAINERNAME,WFS.WORKFLOWSTEPNAME,WFST.WORKFLOWSTEPID,null stepinfoid,WFST.QTY, WFST.SEQUENCE,
        CT.MEASURETOOLID TOOLID, CT.MEASURETOOLNAME TOOLNAME,'cuttool' TYPE ,'刀具' TypeName,
        CT.DESCRIPTION,RT.RESOURCETYPENAME, A.TOOLSTATUS,CT.drawingno,a.arrangementsid 
        FROM WORKFLOWSTEPTOOLS WFST
        LEFT JOIN WORKFLOWSTEP WFS  ON WFS.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID
        LEFT JOIN MEASURETOOL CT  ON CT.MEASURETOOLID = WFST.TOOLS
        LEFT JOIN RESOURCETYPE RT  ON RT.RESOURCETYPEID = CT.RESOURCETYPEID

        LEFT JOIN ARRANGEMENTS A  ON A.CONTAINERID IN ({0}) and A.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID 
        AND A.TOOLNO = CT.MEASURETOOLNAME and  a.stepinfoid is null
  
        LEFT JOIN CONTAINER C  ON C.CONTAINERID = A.CONTAINERID
        WHERE wfst.WORKFLOWSTEPID in ({1})  and CT.MEASURETOOLID != ' ' </string>.Value
        strSQL = String.Format(strSQL, containerID, stepID)
        If Not String.IsNullOrWhiteSpace(ToolNo) Then
            strSQL += String.Format(" and ct.MEASURETOOLNAME='{0}' ", ToolNo)
        End If
        If isCludeStepInfo Then
            strSQL += <string> union all
            SELECT distinct  C.CONTAINERNAME,WFS.WORKFLOWSTEPNAME,wfs.WORKFLOWSTEPID,wfst.stepinfoid,WFST.QTY, WFST.SEQUENCE,
            CT.MEASURETOOLID TOOLID, CT.MEASURETOOLNAME TOOLNAME,'cuttool' TYPE ,'刀具' TypeName,
            CT.DESCRIPTION,RT.RESOURCETYPENAME, A.TOOLSTATUS,CT.drawingno,a.arrangementsid 
            FROM stepinfotools WFST
            left join stepinfo si on si.stepinfoid=wfst.stepinfoid
            LEFT JOIN WORKFLOWSTEP WFS  ON WFS.WORKFLOWSTEPID = si.WORKFLOWSTEPID
            LEFT JOIN MEASURETOOL CT  ON CT.MEASURETOOLID = WFST.TOOLS
            LEFT JOIN RESOURCETYPE RT  ON RT.RESOURCETYPEID = CT.RESOURCETYPEID

            LEFT JOIN ARRANGEMENTS A  ON A.CONTAINERID IN ({0}) and A.WORKFLOWSTEPID = wfs.WORKFLOWSTEPID 
            AND A.TOOLNO = CT.MEASURETOOLNAME and  a.stepinfoid=wfst.stepinfoid
  
            LEFT JOIN CONTAINER C  ON C.CONTAINERID = A.CONTAINERID
            WHERE wfs.WORKFLOWSTEPID in ({1})  and CT.MEASURETOOLID != ' ' </string>.Value
            strSQL = String.Format(strSQL, containerID, stepID)
            If Not String.IsNullOrWhiteSpace(ToolNo) Then
                strSQL += String.Format(" and ct.MEASURETOOLNAME='{0}' ", ToolNo)
            End If
        End If

        Dim measuretooldt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)

        '查询夹模具
        strSQL = <string> SELECT distinct  C.CONTAINERNAME,WFS.WORKFLOWSTEPNAME,WFST.WORKFLOWSTEPID,null stepinfoid,WFST.QTY, WFST.SEQUENCE,
CT.SPECIALTOOLID TOOLID, CT.SPECIALTOOLNAME TOOLNAME,'cuttool' TYPE ,'刀具' TypeName,
CT.DESCRIPTION,RT.RESOURCETYPENAME, A.TOOLSTATUS,CT.drawingno,a.arrangementsid 
FROM WORKFLOWSTEPTOOLS WFST
LEFT JOIN WORKFLOWSTEP WFS  ON WFS.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID
LEFT JOIN SPECIALTOOL CT  ON CT.SPECIALTOOLID = WFST.TOOLS
LEFT JOIN RESOURCETYPE RT  ON RT.RESOURCETYPEID = CT.RESOURCETYPEID

LEFT JOIN ARRANGEMENTS A  ON A.CONTAINERID IN ({0}) and A.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID 
AND A.TOOLNO = CT.SPECIALTOOLNAME and  a.stepinfoid is null
  
LEFT JOIN CONTAINER C  ON C.CONTAINERID = A.CONTAINERID
WHERE wfst.WORKFLOWSTEPID in ({1})  and CT.SPECIALTOOLID != ' ' </string>.Value
        strSQL = String.Format(strSQL, containerID, stepID)
        If Not String.IsNullOrWhiteSpace(ToolNo) Then
            strSQL += String.Format(" and ct.SPECIALTOOLNAME='{0}' ", ToolNo)
        End If

        If isCludeStepInfo Then
            strSQL += <string> union all
            SELECT distinct  C.CONTAINERNAME,WFS.WORKFLOWSTEPNAME,wfs.WORKFLOWSTEPID,wfst.stepinfoid,WFST.QTY, WFST.SEQUENCE,
            CT.SPECIALTOOLID TOOLID, CT.SPECIALTOOLNAME TOOLNAME,'cuttool' TYPE ,'刀具' TypeName,
            CT.DESCRIPTION,RT.RESOURCETYPENAME, A.TOOLSTATUS,CT.drawingno,a.arrangementsid 
            FROM stepinfotools WFST
            left join stepinfo si on si.stepinfoid=wfst.stepinfoid
            LEFT JOIN WORKFLOWSTEP WFS  ON WFS.WORKFLOWSTEPID = si.WORKFLOWSTEPID
            LEFT JOIN SPECIALTOOL CT  ON CT.SPECIALTOOLID = WFST.TOOLS
            LEFT JOIN RESOURCETYPE RT  ON RT.RESOURCETYPEID = CT.RESOURCETYPEID

            LEFT JOIN ARRANGEMENTS A  ON A.CONTAINERID IN ({0}) and A.WORKFLOWSTEPID = wfs.WORKFLOWSTEPID 
            AND A.TOOLNO = CT.SPECIALTOOLNAME and  a.stepinfoid=wfst.stepinfoid
  
            LEFT JOIN CONTAINER C  ON C.CONTAINERID = A.CONTAINERID
            WHERE wfs.WORKFLOWSTEPID in ({1})  and CT.SPECIALTOOLID != ' ' </string>.Value
            strSQL = String.Format(strSQL, containerID, stepID)
            If Not String.IsNullOrWhiteSpace(ToolNo) Then
                strSQL += String.Format(" and ct.SPECIALTOOLNAME='{0}' ", ToolNo)
            End If
        End If

        Dim specialtooldt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)

        If cuttooldt IsNot Nothing Then
            If measuretooldt IsNot Nothing Then
                cuttooldt.Merge(measuretooldt)
            End If

            If specialtooldt IsNot Nothing Then
                cuttooldt.Merge(specialtooldt)
            End If
            If String.IsNullOrWhiteSpace(type) Then
                GetStepToolsData = cuttooldt
            Else
                Dim dv As DataView = cuttooldt.DefaultView
                dv.RowFilter = String.Format("RESOURCETYPENAME='{0}'", type)
                GetStepToolsData = dv.ToTable
            End If

        Else
            GetStepToolsData = New DataTable
        End If

    End Function

    ''' <summary>
    ''' 获取工具SQL
    ''' </summary>
    ''' <param name="stepID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetToolSQL(ByVal stepID As String, ByVal containerID As String, ByVal type As String, ByVal strType As String, ByVal ToolNO As String, Optional ByVal isCludeStepInfo As Boolean = False) As String 'add tianFW 2017/09/25  ByVal type As String  ByVal ToolNO As String
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT distinct  C.CONTAINERNAME,WFS.WORKFLOWSTEPNAME,WFST.WORKFLOWSTEPID, WFST.stepinfoid,WFST.QTY, WFST.SEQUENCE,")
        If strType = "cuttool" Then '刀具
            sb.AppendLine("CT.CUTTOOLID TOOLID, CT.CUTTOOLNAME TOOLNAME,'cuttool' TYPE ,'刀具' TypeName,")
        ElseIf strType = "measuretool" Then '量具
            sb.AppendLine("CT.MEASURETOOLID TOOLID, CT.MEASURETOOLNAME TOOLNAME,'measuretool' TYPE ,'量具' TypeName,")
        ElseIf strType = "specialtool" Then '夹模具
            sb.AppendLine(" CT.SPECIALTOOLID TOOLID, CT.SPECIALTOOLNAME TOOLNAME,'specialtool' TYPE ,'夹模具' TypeName,")
        End If
        sb.AppendLine("CT.DESCRIPTION,RT.RESOURCETYPENAME, A.TOOLSTATUS,CT.drawingno,a.arrangementsid ")
        If type = "工装工具" Then 'add tianFW 2017/09/25
            'sb.AppendLine(",T_.MAKTX TOOLINGNAME,T_.MTART MATERIALTYPE,T_.MENGE QUANTITY,T_.GROES SPECIFICATION，S.SPECID,S.NCCODE, S.NCNUMBER, S.NCREVISION,ST.SPECTYPENAME")
            sb.AppendLine(",T_.MAKTX TOOLINGNAME,T_.MTART MATERIALTYPE,T_.MENGE QUANTITY,T_.GROES SPECIFICATION")
        End If
        'update:Wangjh 考虑工步上的工装
        sb.AppendFormat(<string>FROM (
  select wt.*,null stepinfoid from WORKFLOWSTEPTOOLS wt where WORKFLOWSTEPID in ({0}) </string>.Value, stepID)
        If isCludeStepInfo Then
            sb.AppendFormat(<string> union all
 select st.fieldid,st.sequence,st.tools,ws.workflowstepid,st.qty,st.stepinfoid from stepinfotools st
 left join stepinfo si on si.stepinfoid=st.stepinfoid
 left join workflowstep ws on ws.workflowstepid=si.workflowstepid where ws.workflowstepid in ({0}) </string>.Value, stepID)
        End If
        sb.AppendLine(") WFST")

        sb.AppendLine("  LEFT JOIN WORKFLOWSTEP WFS  ON WFS.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID")
        'Add tianFW 2017/09/26 查询工艺工序准备情况
        'If type = "工装工具" Then
        '    sb.AppendLine("  LEFT JOIN SPECBASE SB ON SB.SPECBASEID = WFS.SPECBASEID")
        '    sb.AppendLine("  LEFT JOIN SPEC S ON S.SPECID =NVL(SB.REVOFRCDID,WFS.SPECID)")
        '    sb.AppendLine(" LEFT JOIN SPECTYPE ST ON ST.SPECTYPEID = S.SPECTYPEID ")
        'End If
        If strType = "cuttool" Then
            sb.AppendLine("  LEFT JOIN CUTTOOL CT  ON CT.CUTTOOLID = WFST.TOOLS")
            If type = "工装工具" Then 'add tianFW 2017/09/25
                sb.AppendLine("LEFT JOIN T_GGDKC T_ ON T_.MATNR = CT.CUTTOOLNAME")
            End If
            sb.AppendLine("  LEFT JOIN RESOURCETYPE RT  ON RT.RESOURCETYPEID = CT.RESOURCETYPEID")
            sb.AppendLine("  LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID and a.stepinfoid=wfst.stepinfoid AND A.TOOLNO = CT.CUTTOOLNAME")
            If Not String.IsNullOrWhiteSpace(containerID) Then
                sb.AppendFormat(" AND A.CONTAINERID IN ({0}) ", containerID)
            End If
            sb.AppendLine("  LEFT JOIN CONTAINER C  ON C.CONTAINERID = A.CONTAINERID")
            sb.AppendFormat("   WHERE CT.CUTTOOLID <> ' ' ")
        ElseIf strType = "measuretool" Then
            sb.AppendLine("  LEFT JOIN MEASURETOOL CT  ON CT.MEASURETOOLID = WFST.TOOLS")
            If type = "工装工具" Then 'add tianFW 2017/09/25
                sb.AppendLine("LEFT JOIN T_GGDKC T_ ON T_.MATNR = CT.MEASURETOOLNAME")
            End If
            sb.AppendLine("  LEFT JOIN RESOURCETYPE RT  ON RT.RESOURCETYPEID = CT.RESOURCETYPEID")
            sb.AppendLine("  LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID and a.stepinfoid=wfst.stepinfoid AND A.TOOLNO = CT.MEASURETOOLNAME")
            If Not String.IsNullOrWhiteSpace(containerID) Then
                sb.AppendFormat(" AND A.CONTAINERID IN ({0}) ", containerID)
            End If
            sb.AppendLine("  LEFT JOIN CONTAINER C  ON C.CONTAINERID = A.CONTAINERID")
            sb.AppendFormat("   WHERE CT.MEASURETOOLID <> ' ' ")
        ElseIf strType = "specialtool" Then
            sb.AppendLine("  LEFT JOIN SPECIALTOOL CT  ON CT.SPECIALTOOLID = WFST.TOOLS")
            If type = "工装工具" Then 'add tianFW 2017/09/25
                sb.AppendLine("LEFT JOIN T_GGDKC T_ ON T_.MATNR = CT.SPECIALTOOLNAME")
            End If
            sb.AppendLine("  LEFT JOIN RESOURCETYPE RT  ON RT.RESOURCETYPEID = CT.RESOURCETYPEID")
            sb.AppendLine("  LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID and a.stepinfoid=wfst.stepinfoid AND A.TOOLNO = CT.SPECIALTOOLNAME")
            If Not String.IsNullOrWhiteSpace(containerID) Then
                sb.AppendFormat(" AND A.CONTAINERID IN ({0}) ", containerID)
            End If
            sb.AppendLine("  LEFT JOIN CONTAINER C  ON C.CONTAINERID = A.CONTAINERID")
            sb.AppendFormat("   WHERE CT.SPECIALTOOLID <> ' ' ")
        End If

        If type = "工装工具" Then 'add tianFW 2017/09/25
            If Not String.IsNullOrWhiteSpace(ToolNO) Then 'Add tianFW 2017/09/26
                sb.AppendFormat("AND T_.MATNR LIKE '%{0}%'", ToolNO) 'modify:wangjh 2019/02/19 T_MATNR->T_.MATNR
            End If
        End If
        sb.AppendLine(" ORDER BY C.CONTAINERNAME,WFST.WORKFLOWSTEPID,WFST.SEQUENCE")
        GetToolSQL = sb.ToString
    End Function

    ''' <summary>
    ''' 获取工序、工步工具信息 create tianFW 2018年4月24日
    ''' </summary>
    ''' <param name="StepIDs"></param>
    ''' <param name="type"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepToolsInfo(ByVal StepIDs As String, ByVal type As String) As DataTable Implements uLMSDispatchIBusiness.GetStepToolsInfo
        Dim strSQL As String = GetToolSQL(StepIDs, type, "cuttool")
        Dim cuttooldt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)

        strSQL = GetToolSQL(StepIDs, type, "measuretool")
        Dim measuretooldt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)

        strSQL = GetToolSQL(StepIDs, type, "specialtool")
        Dim specialtooldt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)

        If cuttooldt IsNot Nothing Then
            If measuretooldt IsNot Nothing Then
                cuttooldt.Merge(measuretooldt)
            End If
            If specialtooldt IsNot Nothing Then
                cuttooldt.Merge(specialtooldt)
            End If
        End If
        GetStepToolsInfo = cuttooldt
    End Function

    ''' <summary>
    ''' 获取工序上的辅材信息
    ''' add:Wangjh 20221031 yuxj增加工步信息
    ''' yuxj20221121增加显示 am.technicalstandard,
    ''' </summary>
    ''' <param name="workflowstepIds"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetAuxToolsInfoByStepID(ByVal workflowstepIds As String, ByVal containerIds As String, Optional ByVal isCludeStepInfo As Boolean = False) As DataTable Implements uLMSDispatchIBusiness.GetAuxToolsInfoByStepID
        Dim strSql As String = <string>select distinct wfs.workflowstepid,wfs.workflowstepname,
am.auxmaterialid,am.auxmaterialname,am.description,am.specification,am.vendormodel,am.technicalstandard, am.notes,sft.qty,at.ToolStatus,at.ArrangementsID,
null stepinfoid,null stepinfodesc,wfs.Sequence,wfs.workflowid,null stepno,null stepseq
from workflowstep wfs                       
inner join workflowsteptools sft on sft.workflowstepid=wfs.workflowstepid
inner join auxmaterial am on am.auxmaterialid=sft.tools
left join arrangements at on at.workflowstepid=wfs.workflowstepid and at.toolno=am.auxmaterialname and at.StepInfoID is null and at.containerid in ({1})
where wfs.workflowstepid in ({0})
 </string>.Value
        If isCludeStepInfo Then
            strSql += <string>
                          union all
select distinct ws.workflowstepid,ws.workflowstepname,am.auxmaterialid,am.auxmaterialname,am.description,
am.specification,am.vendormodel,am.technicalstandard, am.notes,st.qty,at.ToolStatus,at.ArrangementsID,si.stepinfoid,si.stepno||'-'||si.description stepinfodesc,ws.Sequence ,ws.workflowid,si.stepno,si.stepseq
from stepinfo si
inner join stepinfotools st on st.stepinfoid=si.stepinfoid
inner join auxmaterial am on am.auxmaterialid=st.tools
left join workflowstep ws on ws.workflowstepid=si.workflowstepid
left join arrangements at on at.workflowstepid=si.workflowstepid and at.stepinfoid=si.stepinfoid and at.toolno=am.auxmaterialname and at.containerid in ({1})
where si.workflowstepid in ({0})
                      </string>.Value
        End If

        strSql = "select * from (" + strSql + ") order by workflowid,Sequence,stepno,stepseq asc"
        strSql = String.Format(strSql, workflowstepIds, containerIds)
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取工步上的辅材信息
    ''' add:Wangjh 20221031
    ''' </summary>
    ''' <param name="stepInfoIds"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetAuxToolsInfoByStepInfoID(ByVal stepInfoIds As String) As DataTable Implements uLMSDispatchIBusiness.GetAuxToolsInfoByStepInfoID
        Dim strSql As String = <string> select ws.workflowstepid,ws.workflowstepname,si.stepinfoid,si.stepno||'-'||si.description stepinfodesc,am.auxmaterialid,am.auxmaterialname,am.description,
am.specification,am.vendormodel, am.notes from stepinfo si
left join workflowstep ws on ws.workflowstepid=si.workflowstepid
inner join stepinfotools st on st.stepinfoid=si.stepinfoid
inner join auxmaterial am on am.auxmaterialid=st.tools
where si.stepinfoid in ({0})
order by ws.sequence, si.stepno, si.stepseq
 </string>.Value
        strSql = String.Format(strSql, stepInfoIds)
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取工具SQL create tianFW 2018年4月24日
    ''' </summary>
    ''' <param name="stepIDs"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetToolSQL(ByVal stepIDs As String, ByVal type As String, ByVal strType As String) As String
        Dim sb As New StringBuilder
        If type = "WorkflowStep" Then '工序
            sb.AppendLine("SELECT  WS.WORKFLOWSTEPNAME,'' STEPINFONAME,WS.WORKFLOWSTEPID, T.QTY, T.SEQUENCE,")
        ElseIf type = "StepInfo" Then '工步
            sb.AppendLine("SELECT  WS.WORKFLOWSTEPNAME,SIOB.STEPINFONAME,ST.SPECTYPENAME,SIO.STEPNO||'-'||sio.description stepinfodesc,WS.WORKFLOWSTEPID,T.QTY,T.SEQUENCE,")
        End If
        If strType = "cuttool" Then '刀具
            sb.AppendLine("CT.CUTTOOLID TOOLID, CT.CUTTOOLNAME TOOLNAME,'cuttool' TYPE ,")
        ElseIf strType = "measuretool" Then '量具
            sb.AppendLine("CT.MEASURETOOLID TOOLID, CT.MEASURETOOLNAME TOOLNAME,'measuretool' TYPE ,")
        ElseIf strType = "specialtool" Then '夹模具
            sb.AppendLine("CT.SPECIALTOOLID TOOLID, CT.SPECIALTOOLNAME TOOLNAME,'specialtool' TYPE ,")
        End If
        'yuxj20221121增加显示规格ct.specification 到界面
        sb.AppendLine("CT.Drawingno||'/'||ct.specification,CT.DESCRIPTION,RT.RESOURCETYPENAME")
        If type = "WorkflowStep" Then '工序
            sb.AppendLine("FROM WORKFLOWSTEPTOOLS T")
            sb.AppendLine("LEFT JOIN WORKFLOWSTEP WS  ON WS.WORKFLOWSTEPID = T.WORKFLOWSTEPID")
        ElseIf type = "StepInfo" Then '工步
            sb.AppendLine("FROM STEPINFOTOOLS T")
            sb.AppendLine("LEFT JOIN STEPINFO SIO ON SIO.STEPINFOID = T.STEPINFOID")
            sb.AppendLine("LEFT JOIN WORKFLOWSTEP WS  ON WS.WORKFLOWSTEPID = SIO.WORKFLOWSTEPID")
            sb.AppendLine("LEFT JOIN STEPINFOBASE SIOB  ON SIOB.STEPINFOBASEID = SIO.STEPINFOBASEID")
            sb.AppendLine("LEFT JOIN SPECTYPE ST ON ST.SPECTYPEID = SIO.SPECTYPEID ")
        End If
        If strType = "cuttool" Then
            sb.AppendLine("  LEFT JOIN CUTTOOL CT  ON CT.CUTTOOLID = T.TOOLS")
            sb.AppendLine("  LEFT JOIN RESOURCETYPE RT  ON RT.RESOURCETYPEID = CT.RESOURCETYPEID")
            sb.AppendFormat("   WHERE CT.CUTTOOLID <> ' ' ")
        ElseIf strType = "measuretool" Then
            sb.AppendLine("  LEFT JOIN MEASURETOOL CT  ON CT.MEASURETOOLID = T.TOOLS")
            sb.AppendLine("  LEFT JOIN RESOURCETYPE RT  ON RT.RESOURCETYPEID = CT.RESOURCETYPEID")
            sb.AppendFormat("   WHERE CT.MEASURETOOLID <> ' ' ")
        ElseIf strType = "specialtool" Then
            sb.AppendLine("  LEFT JOIN SPECIALTOOL CT  ON CT.SPECIALTOOLID = T.TOOLS")
            sb.AppendLine("  LEFT JOIN RESOURCETYPE RT  ON RT.RESOURCETYPEID = CT.RESOURCETYPEID")
            sb.AppendFormat("   WHERE CT.SPECIALTOOLID <> ' ' ")
        End If
        sb.AppendLine("AND (")
        Dim strIDs As String() = stepIDs.Split(",")
        Dim valuestr As New StringBuilder
        For i As Integer = 0 To strIDs.Length - 1
            If type = "WorkflowStep" Then '工序
                If valuestr.Length > 0 Then
                    valuestr.AppendLine("OR")
                End If
                valuestr.AppendFormat(" T.WORKFLOWSTEPID='{0}' ", strIDs(i))
            ElseIf type = "StepInfo" Then '工步
                If valuestr.Length > 0 Then
                    valuestr.AppendLine("OR")
                End If
                valuestr.AppendFormat("T.STEPINFOID='{0}'", strIDs(i))
            End If
        Next
        sb.AppendLine(valuestr.ToString() & ")")
        If type = "WorkflowStep" Then '工序
            sb.AppendLine(" ORDER BY WS.SEQUENCE,T.SEQUENCE")
        ElseIf type = "StepInfo" Then '工步
            sb.AppendLine(" ORDER BY WS.SEQUENCE,SIO.STEPNO,SIO.STEPSEQ,T.SEQUENCE")
        End If
        GetToolSQL = sb.ToString
    End Function

    ''' <summary>
    ''' 保存生产准备申请数据 V2.0
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveArrangementsData(ByVal QueryCondition As System.Collections.Generic.Dictionary(Of String, Object)) As Boolean Implements IBusiness.uLMSDispatchIBusiness.SaveArrangementsData
        Dim strSQL As String =
            <string>
                    INSERT INTO ARRANGEMENTS
                      (CONTAINERID,QTY,TOOLNAME, TOOLNO, TOOLTYPE,TOOLSTATUS,CREATEDATE,ISINTEGRATION,
                        EMPLOYEEID, MFGORDERNAME, NEEDDATE, TEAMID,WORKFLOWSTEPID,ARRANGEMENTSID,ISTRANSFER,ARRANGEMENTSID2,WORKFLOWID,ApplyType,StepInfoID)
                    VALUES
                      (:V_CONTAINERID,:V_QTY,:V_TOOLNAME,:V_TOOLNO,:V_TOOLTYPE,'待准备',sysdate,'1',
                       :V_EMPLOYEEID,:V_MFGORDERNAME,:V_NEEDDATE,:V_TEAMID,:V_WORKFLOWSTEPID,:V_ARRANGEMENTSID,0,:ARRANGEMENTSID2,:WORKFLOWID,:ApplyType,:StepInfoID)
             </string>
        Dim oraPara(14) As OracleParameter
        'modify tianFW 2017/10/10 优化传入参数 
        Dim ID = Guid.NewGuid().ToString
        oraPara(0) = DatabaseHelper.NewParameter(":V_CONTAINERID", QueryCondition("ContainerID"), OracleType.VarChar)
        oraPara(1) = DatabaseHelper.NewParameter(":V_QTY", QueryCondition("Qty"), OracleType.Number)
        oraPara(2) = DatabaseHelper.NewParameter(":V_TOOLNAME", QueryCondition("ToolName"), OracleType.VarChar)
        oraPara(3) = DatabaseHelper.NewParameter(":V_TOOLNO", QueryCondition("ToolNo"), OracleType.VarChar)
        oraPara(4) = DatabaseHelper.NewParameter(":V_TOOLTYPE", QueryCondition("ToolType"), OracleType.VarChar)
        oraPara(5) = DatabaseHelper.NewParameter(":V_EMPLOYEEID", QueryCondition("EmployeeID"), OracleType.VarChar)
        oraPara(6) = DatabaseHelper.NewParameter(":V_MFGORDERNAME", QueryCondition("MfgOrderName"), OracleType.VarChar)
        oraPara(7) = DatabaseHelper.NewParameter(":V_NEEDDATE", QueryCondition("NeedDate"), OracleType.DateTime)
        oraPara(8) = DatabaseHelper.NewParameter(":V_TEAMID", QueryCondition("TeamID"), OracleType.VarChar)
        oraPara(9) = DatabaseHelper.NewParameter(":V_WORKFLOWSTEPID", QueryCondition("StepID"), OracleType.VarChar)
        oraPara(10) = DatabaseHelper.NewParameter(":V_ARRANGEMENTSID", ID, OracleType.VarChar)
        oraPara(11) = DatabaseHelper.NewParameter(":ARRANGEMENTSID2", QueryCondition("ID2"), OracleType.VarChar)
        oraPara(12) = DatabaseHelper.NewParameter(":WORKFLOWID", QueryCondition("WorkflowID"), OracleType.VarChar)
        oraPara(13) = DatabaseHelper.NewParameter(":ApplyType", QueryCondition("ApplyType"), OracleType.VarChar)
        oraPara(14) = DatabaseHelper.NewParameter(":StepInfoID", IIf(String.IsNullOrWhiteSpace(QueryCondition("StepInfoID")), "", QueryCondition("StepInfoID")), OracleType.VarChar)
        'add YangJiang 20180516 将数据存入ArrangementsContainer表
        Dim str As String =
            <string>
                INSERT INTO ArrangementsContainer (ARRANGEMENTSID,CONTAINERID) VALUES (:ARRANGEMENTSID,:CONTAINERID)
            </string>
        Dim Para(1) As OracleParameter
        Para(0) = DatabaseHelper.NewParameter(":CONTAINERID", QueryCondition("ContainerID"), OracleType.VarChar)
        Para(1) = DatabaseHelper.NewParameter(":ARRANGEMENTSID", ID, OracleType.VarChar)
        If QueryCondition.Keys.Contains("ContainerType") Then
            DatabaseHelper.ExecuteSql(str, Para)
        End If


        Dim i As Integer = DatabaseHelper.ExecuteSql(strSQL, oraPara)
        If i = 0 Then
            SaveArrangementsData = False
        Else
            SaveArrangementsData = True
        End If
    End Function

    ''' <summary>
    ''' 协作接收V2.0
    ''' create:Wangjh
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function UpdateContainerWorkCenterDispatch(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSDispatchIBusiness.UpdateContainerWorkCenterDispatch

        Dim strSql As String = String.Format("UPDATE containerworkcenterdispatch cwcd SET RECVEMPLOYEEID=:RECVEMPLOYEEID,RECVDATE=sysDate,ISTRANSFER=0" &
                                            "  WHERE cwcd.CONTAINERWORKCENTERDISPATCHID=:CONTAINERWORKCENTERDISPATCHID" &
                                            " AND cwcd.toworkcenterid=:workcenterID")
        Dim updateSql(2) As OracleParameter
        'modify tianFW 2017/10/10 优化插入语句的参数传入 
        updateSql(0) = DatabaseHelper.NewParameter(":RecvEmployeeID", para("RecvEmployeeID"), OracleType.VarChar)
        updateSql(1) = DatabaseHelper.NewParameter(":CONTAINERWORKCENTERDISPATCHID", para("ContainerWorkCenterDispatchID"), OracleType.VarChar)
        updateSql(2) = DatabaseHelper.NewParameter(":workcenterID", para("workcenterID"), OracleType.VarChar)

        Dim updateCurrentInfoSql As String = " UPDATE CONTAINERCURRENTINFO CCI SET TOWORKCENTERID = '', CCI.WORKCENTERDISPATCHNUM = 0 WHERE CCI.WORKCENTERDISPATCHID = :WORKCENTERDISPATCHID"
        Dim updateCurrentInfoPara(0) As OracleParameter
        updateCurrentInfoPara(0) = DatabaseHelper.NewParameter(":WORKCENTERDISPATCHID", para("ContainerWorkCenterDispatchID"), OracleType.VarChar)

        If (DatabaseHelper.ExecuteSql(strSql, updateSql) > 0) Then
            'If (DatabaseHelper.ExecuteSql(updateCurrentInfoSql, updateCurrentInfoPara) >= 0) Then
            '    Return True
            'Else
            '    Return False
            'End If
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 添加批次工区协作V2.0
    ''' create tianFW
    ''' </summary>
    ''' <remarks></remarks>
    Public Function AddContainerWorkCenterDispatchInfo(ByVal entity As ContainerWorkCenterDispatchDTO) As Boolean Implements IBusiness.uLMSDispatchIBusiness.AddContainerWorkCenterDispatchInfo

        Dim strsql As String = "INSERT INTO CONTAINERWORKCENTERDISPATCH(CONTAINERID,FACTORYID,WORKCENTERID,TOWORKCENTERID,WORKFLOWSTEPID,CONTAINERWORKCENTERDISPATCHID,EMPLOYEEID,CREATEDATE,ISUSE,ISTRANSFER,COMPLETEDATE,estimatedsenddate)"
        strsql += "VALUES(:containerid,:factoryid,:workcenterid,:Toworkcenterid,:workflowstepid,:containerworkcenterdispatchid,:employeeid,:createdate,:isuse,0,:COMPLETEDATE,:estimatedsenddate)"

        Dim insertParam(10) As OracleParameter
        'modify tianFW 2017/10/10 优化插入语句的参数传入 
        insertParam(0) = DatabaseHelper.NewParameter(":containerid", entity.ContainerID, OracleType.VarChar)
        insertParam(1) = DatabaseHelper.NewParameter(":factoryid", entity.FactoryID, OracleType.VarChar)
        insertParam(2) = DatabaseHelper.NewParameter(":workcenterid", entity.WorkCenterID, OracleType.VarChar)
        insertParam(3) = DatabaseHelper.NewParameter(":Toworkcenterid", entity.ToWorkcenterID, OracleType.VarChar)
        'Modify by Yuxj20180628
        insertParam(4) = DatabaseHelper.NewParameter(":workflowstepid",
                                                     IIf(entity.WorkflowstepID Is Nothing, String.Empty, entity.WorkflowstepID), OracleType.VarChar)
        insertParam(5) = DatabaseHelper.NewParameter(":containerworkcenterdispatchid", entity.ContainerWorkCenterDispatchID, OracleType.VarChar)
        insertParam(6) = DatabaseHelper.NewParameter(":employeeid", entity.EmployeeID, OracleType.VarChar)
        insertParam(7) = DatabaseHelper.NewParameter(":createdate", System.DateTime.Now, OracleType.DateTime)
        insertParam(8) = DatabaseHelper.NewParameter(":isuse", 0, OracleType.Number)
        insertParam(9) = DatabaseHelper.NewParameter(":COMPLETEDATE", entity.CompleteDate, OracleType.DateTime)
        insertParam(10) = DatabaseHelper.NewParameter(":estimatedsenddate", entity.EstimatedSendDate, OracleType.DateTime)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertParam)
        If (rows = 0) Then
            Return False
        End If
        Return True
    End Function

    ''' <summary>
    ''' 添加批次班组派工 V2.0
    ''' create tianFW
    ''' </summary>
    ''' <remarks></remarks>
    Public Function AddContainerTeamDispatchInfo(ByVal entity As ContainerTeamDispatchDTO) As Boolean Implements IBusiness.uLMSDispatchIBusiness.AddContainerTeamDispatchInfo
        Dim strsql As String
        If entity.ShiftID Is Nothing Then
            strsql = "INSERT INTO CONTAINERTEAMDISPATCH(CONTAINERID,FACTORYID,WORKCENTERID,TEAMID,WORKFLOWSTEPID,CONTAINERTEAMDISPATCHID,EMPLOYEEID,CREATEDATE,ISUSE,ISTRANSFER)"
            strsql += "VALUES(:containerid,:factoryid,:workcenterid,:teamid,:workflowstepid,:containerteamdispatchid,:employeeid,:createdate,:isuse,0)"
        ElseIf entity.TeamID Is Nothing Then
            strsql = "INSERT INTO CONTAINERTEAMDISPATCH(CONTAINERID,FACTORYID,WORKCENTERID,SHIFTID,WORKFLOWSTEPID,CONTAINERTEAMDISPATCHID,EMPLOYEEID,CREATEDATE,ISUSE,ISTRANSFER)"
            strsql += "VALUES(:containerid,:factoryid,:workcenterid,:shiftid,:workflowstepid,:containerteamdispatchid,:employeeid,:createdate,:isuse,1)"
        Else
            strsql = "INSERT INTO CONTAINERTEAMDISPATCH(CONTAINERID,FACTORYID,WORKCENTERID,SHIFTID,WORKFLOWSTEPID,CONTAINERTEAMDISPATCHID,EMPLOYEEID,CREATEDATE,ISUSE,ISTRANSFER,Toteamdispatchdate,Teamid,Toteamdispatcherid)"
            strsql += "VALUES(:containerid,:factoryid,:workcenterid,:shiftid,:workflowstepid,:containerteamdispatchid,:employeeid,:createdate,:isuse,1,:Toteamdispatchdate,:Teamid,:Toteamdispatcherid)"
        End If

        Dim i_Length As Integer = 8
        If (Not IsNothing(entity.ShiftID)) And (Not IsNothing(entity.TeamID)) Then
            i_Length = 11
        End If
        Dim insertParam(i_Length) As OracleParameter 'modify 2017/10/10
        insertParam(0) = DatabaseHelper.NewParameter(":containerid", entity.ContainerID, OracleType.VarChar)
        insertParam(1) = DatabaseHelper.NewParameter(":factoryid", entity.FactoryID, OracleType.VarChar)
        insertParam(2) = DatabaseHelper.NewParameter(":workcenterid", entity.WorkCenterID, OracleType.VarChar)
        If entity.ShiftID Is Nothing Then
            insertParam(3) = DatabaseHelper.NewParameter(":teamid", entity.TeamID, OracleType.VarChar)
        Else
            insertParam(3) = DatabaseHelper.NewParameter(":shiftid", entity.ShiftID, OracleType.VarChar)
        End If
        'If entity.WorkflowstepID Is Nothing Then
        '    insertParam(4) = DatabaseHelper.NewParameter(":workflowstepid", String.Empty, OracleType.VarChar)
        'Else
        '    insertParam(4) = DatabaseHelper.NewParameter(":workflowstepid", entity.WorkflowstepID, OracleType.VarChar)
        'End If
        insertParam(4) = DatabaseHelper.NewParameter(":workflowstepid",
                                                      IIf(entity.WorkflowstepID Is Nothing, String.Empty, entity.WorkflowstepID), OracleType.VarChar)

        insertParam(5) = DatabaseHelper.NewParameter(":containerteamdispatchid", entity.ContainerTeamDispatchID, OracleType.VarChar)
        insertParam(6) = DatabaseHelper.NewParameter(":employeeid", entity.EmployeeID, OracleType.VarChar)
        insertParam(7) = DatabaseHelper.NewParameter(":createdate", System.DateTime.Now, OracleType.DateTime)
        insertParam(8) = DatabaseHelper.NewParameter(":isuse", 0, OracleType.Number)

        If (Not IsNothing(entity.ShiftID)) And (Not IsNothing(entity.TeamID)) Then
            insertParam(9) = DatabaseHelper.NewParameter(":Toteamdispatchdate", System.DateTime.Now, OracleType.DateTime)
            insertParam(10) = DatabaseHelper.NewParameter(":TeamId", entity.TeamID, OracleType.VarChar)
            insertParam(11) = DatabaseHelper.NewParameter(":Toteamdispatcherid", entity.EmployeeID, OracleType.VarChar)
        End If

        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertParam)
        If (rows = 0) Then
            Return False
        End If
        Return True
    End Function

    ''' <summary>
    ''' 更新批次班组派工数据V2.0
    ''' create：wangjh
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdatetContainerTeamDispatch(ByVal teamID As String, ByVal workcenterID As String, ByVal containerTeamDispatchID As String) As Boolean Implements uLMSDispatchIBusiness.UpdatetContainerTeamDispatch
        Dim strSql As String = String.Format("UPDATE CONTAINERTEAMDISPATCH CTD SET TEAMID = :TEAMID,workcenterid=:workcenterid,ISTRANSFER=0 WHERE CTD.CONTAINERTEAMDISPATCHID = :CONTAINERTEAMDISPATCHID")

        Dim updatetParam(2) As OracleParameter
        'modify tianFW 2017/10/10 优化传入参数语句
        updatetParam(0) = DatabaseHelper.NewParameter(":TEAMID", teamID, OracleType.VarChar)
        updatetParam(1) = DatabaseHelper.NewParameter(":CONTAINERTEAMDISPATCHID", containerTeamDispatchID, OracleType.VarChar)
        updatetParam(2) = DatabaseHelper.NewParameter(":workcenterid", workcenterID, OracleType.VarChar)

        Dim rows = DatabaseHelper.ExecuteSql(strSql, updatetParam)
        If rows > 0 Then
            UpdatetContainerTeamDispatch = True
            '更新总表
            m_ConCurrentinfoObj.UpdateTeamDispach("", containerTeamDispatchID)
            '
            Exit Function
        End If
        UpdatetContainerTeamDispatch = False
    End Function

    ''' <summary>
    ''' 添加批次工序派工
    ''' create tianFW
    ''' </summary>
    ''' <remarks></remarks>
    Public Function AddContainerStepDispatchInfo(ByVal entity As ContainerStepDispatchDTO) As Boolean Implements IBusiness.uLMSDispatchIBusiness.AddContainerStepDispatchInfo
        Dim objPara As Object()
        objPara = FillContainerStepDispatchInfo(entity)

        Dim rows As Integer = DatabaseHelper.ExecuteSql(objPara.First, objPara.Last)
        If (rows = 0) Then
            Return False
        End If
        Return True
    End Function

    Public Function FillContainerStepDispatchInfo(ByVal entity As ContainerStepDispatchDTO) As Object() Implements IBusiness.uLMSDispatchIBusiness.FillContainerStepDispatchInfo
        Dim strsql As String
        Dim length As Integer = 0
        If entity.stepID Is Nothing Then
            strsql = "INSERT INTO CONTAINERSTEPDISPATCH(CONTAINERID,FACTORYID,WORKCENTERID,TEAMID,WORKFLOWSTEPID,RESOURCEID,WORKERID,QTY,PLANNEDSTARTDATE,PLANNEDFINISHDATE,CONTAINERSTEPDISPATCHID,EMPLOYEEID,CREATEDATE,ISUSE,ISTRANSFER,PRIORITYCODE)"
            strsql += "VALUES(:containerid,:factoryid,:workcenterid,:teamid,:workflowstepid,:resourceid,:workerid,:qty,:plannedstartdate,:plannedfinishdate,:containerstepdispatchid,:employeeid,:createdate,:isuse,0,:PRIORITYCODE)"
            length = 14
        Else
            strsql = "INSERT INTO CONTAINERSTEPDISPATCH(CONTAINERID,FACTORYID,WORKCENTERID,TEAMID,WORKFLOWSTEPID,RESOURCEID,WORKERID,QTY,PLANNEDSTARTDATE,PLANNEDFINISHDATE,CONTAINERSTEPDISPATCHID,EMPLOYEEID,CREATEDATE,ISUSE,ISTRANSFER,PRIORITYCODE,STEPINFOID)"
            strsql += "VALUES(:containerid,:factoryid,:workcenterid,:teamid,:workflowstepid,:resourceid,:workerid,:qty,:plannedstartdate,:plannedfinishdate,:containerstepdispatchid,:employeeid,:createdate,:isuse,0,:PRIORITYCODE,:STEPINFOID)"
            length = 15
        End If
        Dim insertParam(length) As OracleParameter
        'modify 2017/10/10
        insertParam(0) = DatabaseHelper.NewParameter(":containerid", entity.ContainerID, OracleType.VarChar)
        insertParam(1) = DatabaseHelper.NewParameter(":factoryid", entity.FactoryID, OracleType.VarChar)
        insertParam(2) = DatabaseHelper.NewParameter(":workcenterid", entity.WorkCenterID, OracleType.VarChar)
        insertParam(3) = DatabaseHelper.NewParameter(":teamid", entity.TeamID, OracleType.VarChar)
        If entity.ResourceID = "" Then
            insertParam(4) = DatabaseHelper.NewParameter(":resourceid", DBNull.Value, OracleType.VarChar)
        Else
            insertParam(4) = DatabaseHelper.NewParameter(":resourceid", entity.ResourceID, OracleType.VarChar)
        End If
        insertParam(5) = DatabaseHelper.NewParameter(":workerid", entity.WorkerID, OracleType.VarChar)
        insertParam(6) = DatabaseHelper.NewParameter(":qty", entity.Qty, OracleType.Number)
        If entity.PlannedStartDate = DateTime.Parse("0001/01/01") Then
            insertParam(7) = DatabaseHelper.NewParameter(":plannedstartdate", DBNull.Value, OracleType.DateTime)
        Else
            insertParam(7) = DatabaseHelper.NewParameter(":plannedstartdate", entity.PlannedStartDate, OracleType.DateTime)
        End If
        If entity.PlannedFinishDate = DateTime.Parse("0001/01/01") Then
            insertParam(8) = DatabaseHelper.NewParameter(":plannedfinishdate", DBNull.Value, OracleType.DateTime)
        Else
            insertParam(8) = DatabaseHelper.NewParameter(":plannedfinishdate", entity.PlannedFinishDate, OracleType.DateTime)
        End If

        insertParam(9) = DatabaseHelper.NewParameter(":containerstepdispatchid", entity.ContainerStepDispatchID, OracleType.VarChar)
        insertParam(10) = DatabaseHelper.NewParameter(":employeeid", entity.EmployeeID, OracleType.VarChar)
        insertParam(11) = DatabaseHelper.NewParameter(":createdate", System.DateTime.Now, OracleType.DateTime)
        'Modify by Yuxj20180628
        'insertParam(12) = DatabaseHelper.NewParameter(":workflowstepid", entity.WorkflowstepID, OracleType.VarChar)
        insertParam(12) = DatabaseHelper.NewParameter(":workflowstepid",
                                                     IIf(entity.WorkflowstepID Is Nothing, String.Empty, entity.WorkflowstepID), OracleType.VarChar)

        insertParam(13) = DatabaseHelper.NewParameter(":Isuse", 0, OracleType.Number)
        insertParam(14) = DatabaseHelper.NewParameter(":PRIORITYCODE", entity.PriorityCode, OracleType.Number)
        If length = 15 Then
            insertParam(15) = DatabaseHelper.NewParameter(":STEPINFOID", entity.stepID, OracleType.VarChar)
        End If
        Return New Object() {strsql, insertParam}
    End Function

    ''' <summary>
    ''' 按唯一ID删除批次工区协作V2.0
    ''' create tianFW
    ''' </summary>
    ''' <remarks></remarks> 
    Public Function DelContainerWorkCenterDispatchBycwdid(ByVal containerworkcenterdispatchid As String) As Boolean Implements IBusiness.uLMSDispatchIBusiness.DelContainerWorkCenterDispatchBycwdid
        Dim strsql As String = String.Format("UPDATE CONTAINERWORKCENTERDISPATCH C SET ISUSE=1,ISTRANSFER=0 WHERE C.CONTAINERWORKCENTERDISPATCHID = '{0}'", containerworkcenterdispatchid)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql)
        If rows > 0 Then
            Return True
        End If
        Return False
    End Function

    ''' <summary>
    ''' 按唯一ID删除批次班组派工
    ''' create tianFW
    ''' </summary>
    ''' <remarks></remarks>
    Public Function DelContainerTeamDispatchByctdid(ByVal containerteamdispatchid As String) As Boolean Implements IBusiness.uLMSDispatchIBusiness.DelContainerTeamDispatchByctdid
        Dim strsql As String = String.Format("DELETE CONTAINERTEAMDISPATCH C WHERE C.CONTAINERTEAMDISPATCHID = '{0}'", containerteamdispatchid)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql)
        If (rows = 0) Then
            Return False
        End If
        Return True
    End Function

    ''' <summary>
    ''' 按唯一ID删除批次工序派工 create by tianFW
    ''' </summary>
    ''' <param name="containerStepDispatchID">唯一ID</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DelContainerStepDispatchBycsdid(ByVal containerStepDispatchID As String) As Boolean Implements IBusiness.uLMSDispatchIBusiness.DelContainerStepDispatchBycsdid
        Dim strsql As String = String.Format("UPDATE CONTAINERSTEPDISPATCH C SET C.ISUSE=1 WHERE C.CONTAINERSTEPDISPATCHID = '{0}'", containerStepDispatchID)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql)
        If (rows = 0) Then
            Return False
        Else
            Dim conCurrObjectBus As New uLMSContainercurrentinfoBusiness
            conCurrObjectBus.GetAndDoWithDispatchDelInfo(containerStepDispatchID)
        End If
        Return True
    End Function

    ''' <summary>
    ''' 更新工序派工
    ''' create tianFW
    ''' </summary>
    ''' <param name="UpdateCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateContainerStepDispatch(ByVal UpdateCondition As System.Collections.Generic.Dictionary(Of String, Object)) As Boolean Implements IBusiness.uLMSDispatchIBusiness.UpdateContainerStepDispatch
        Dim strsql As String = "UPDATE CONTAINERSTEPDISPATCH C "
        strsql += "SET C.TEAMID=:teamid,C.RESOURCEID=:resourceid,C.WORKERID=:employeeid,C.QTY=:qty,C.PLANNEDSTARTDATE=:plannedstartdate,C.PLANNEDFINISHDATE=:plannedfinishdate,C.ISTRANSFER=0,"
        strsql += "C.PRIORITYCODE=:PRIORITYCODE WHERE C.CONTAINERSTEPDISPATCHID=:containerstepdispatchid"
        Dim updatepar(7) As OracleParameter
        updatepar(0) = DatabaseHelper.NewParameter(":teamid", UpdateCondition("teamid"), OracleType.VarChar)
        updatepar(1) = DatabaseHelper.NewParameter(":resourceid", UpdateCondition("resourceid"), OracleType.VarChar)
        updatepar(2) = DatabaseHelper.NewParameter(":employeeid", UpdateCondition("employeeid"), OracleType.VarChar)
        updatepar(3) = DatabaseHelper.NewParameter(":qty", UpdateCondition("qty"), OracleType.Number)
        If UpdateCondition("plannedstartdate") = DateTime.Parse("0001/01/01") Then
            updatepar(4) = DatabaseHelper.NewParameter(":plannedstartdate", DBNull.Value, OracleType.DateTime)
        Else
            updatepar(4) = DatabaseHelper.NewParameter(":plannedstartdate", UpdateCondition("plannedstartdate"), OracleType.DateTime)
        End If
        If UpdateCondition("plannedfinishdate") = DateTime.Parse("0001/01/01") Then
            updatepar(5) = DatabaseHelper.NewParameter(":plannedfinishdate", DBNull.Value, OracleType.DateTime)
        Else
            updatepar(5) = DatabaseHelper.NewParameter(":plannedfinishdate", UpdateCondition("plannedfinishdate"), OracleType.DateTime)
        End If
        updatepar(6) = DatabaseHelper.NewParameter(":PRIORITYCODE", UpdateCondition("PRIORITYCODE"), OracleType.Number)
        updatepar(7) = DatabaseHelper.NewParameter(":containerstepdispatchid", UpdateCondition("containerstepdispatchid"), OracleType.VarChar)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, updatepar)
        If (rows = 0) Then
            Return False
        End If
        Return True
    End Function

    ''' <summary>
    ''' 获取批次已派工数
    ''' creat:Wangjh 2017-6-15
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks>add and csd.isuse = 0,WHERE CSW.ISUSE = 0  by tianFW 2018年9月29日</remarks>
    Public Function GetContainerQtyOfDispach(ByVal containerID As String) As DataTable Implements uLMSDispatchIBusiness.GetContainerQtyOfDispach
        Dim strSql As String = " SELECT sum(CSD.QTY-a.sumlossqty) qty,CSD.WORKFLOWSTEPID FROM CONTAINERSTEPDISPATCH CSD" &
                                " LEFT JOIN (SELECT SUM(csw.lossqty) sumlossqty,csw.containerstepdispatchid FROM containerstepworkreport csw" &
                                " WHERE CSW.ISUSE = 0 GROUP BY csw.containerstepdispatchid" &
                                " ) a ON a.containerstepdispatchid=csd.containerstepdispatchid"
        strSql += String.Format(" WHERE CSD.CONTAINERID IN ({0}) and csd.isuse = 0 ", containerID)
        strSql += " GROUP BY CSD.WORKFLOWSTEPID"
        GetContainerQtyOfDispach = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取指定批次的工区协作信息或者转工信息
    ''' create:Wangjh 
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkCenterORShipInfo(ByVal para As Dictionary(Of String, String), ByVal type As Integer) As DataTable Implements uLMSDispatchIBusiness.GetWorkCenterORShipInfo
        Dim strSql As String = ""
        If type = 0 Then '查工区协作信息
            strSql += String.Format(" SELECT * FROM CONTAINERWORKCENTERDISPATCH CWD " &
                                   "  LEFT JOIN Workflowstep ws ON ws.workflowstepid=cwd.workflowstepid" &
                                   " WHERE CWD.CONTAINERID  IN ({0}) AND CWD.ISUSE=0  AND WS.SEQUENCE>={1}",
                                   para("ContainerID"), para("Sequence")) 'GetWorkCenterORShipInfo, para("WorkCenterID"),
        End If
        If type = 1 Then '查转工信息
            strSql += String.Format("  SELECT WS.STEPTYPE,CSRS.WORKFLOWSTEPID, CSR.* FROM containercurrentinfo  cci" &
                                    " left join CONTAINERSHIPRECORD csr on csr.CONTAINERSHIPRECORDid=cci.shipid" &
                        " LEFT JOIN CONTAINERSHIPRECORDSTEP CSRS ON CSRS.CONTAINERSHIPRECORDID = CSR.CONTAINERSHIPRECORDID" &
                        "  LEFT JOIN Workflowstep ws ON ws.workflowstepid=csrs.workflowstepid" &
                        "  WHERE cci.CONTAINERID in ({0}) AND CSR.TOFACTORYID = '{1}' AND CSR.ISUSE = 0 AND WS.SEQUENCE>={2} AND (CSR.AUDITRESULT IS NULL OR CSR.AUDITRESULT =0)",
                        para("ContainerID"), para("FactoryID"), para("Sequence"))
        End If
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        GetWorkCenterORShipInfo = dt
    End Function

    ''' <summary>
    ''' 获取已经工区协作的工序 create tianFW
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkCenterWorkflowStepIDs(ByVal ContainerID As String) As DataTable Implements IBusiness.uLMSDispatchIBusiness.GetWorkCenterWorkflowStepIDs
        Dim strsql As String = "SELECT CC.CONTAINERID, CC.WORKFLOWSTEPID FROM CONTAINERWORKCENTERDISPATCH CC"
        strsql += String.Format(" LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CC.WORKFLOWSTEPID WHERE {0} AND CC.ISUSE = 0 AND CC.RecvEmployeeID IS NULL", ContainerID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 添加派工详细信息 add tianFW 2017-08-08
    ''' </summary>
    ''' <param name="ContainerStepDispatchID"></param>
    ''' <param name="Workerid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddDispatchdetails(ByVal ContainerStepDispatchID As String, ByVal Workerid As String, Optional ByVal isZS As Boolean = False) As Boolean Implements IBusiness.uLMSDispatchIBusiness.AddDispatchdetails
        Dim objpara As Object()
        objpara = FillDispatchdetails(ContainerStepDispatchID, Workerid, isZS)
        If DatabaseHelper.ExecuteSql(objpara.First, objpara.Last) > 0 Then
            Return True
        Else
            Return False

        End If
    End Function

    Public Function FillDispatchdetails(ByVal ContainerStepDispatchID As String, ByVal Workerid As String, Optional ByVal isZS As Boolean = False) As Object() Implements IBusiness.uLMSDispatchIBusiness.FillDispatchdetails
        Dim strsql As New StringBuilder
        'add:Wangjh 20190403增加重复记录判断
        'strsql.AppendFormat("select *  from CONTAINERSTEPDISPATCHDETAIL where CONTAINERSTEPDISPATCHID='{0}' and WORKERID='{1}'", ContainerStepDispatchID, Workerid)
        'If DatabaseHelper.Query_ReturnDatTable(strsql.ToString).Rows.Count > 0 Then '已有
        '    Return f
        'End If
        '

        'add:Wangjh 20220608增加重复记录处理
        strsql.Clear()
        strsql.AppendLine(<string> begin delete CONTAINERSTEPDISPATCHDETAIL where CONTAINERSTEPDISPATCHID=:CONTAINERSTEPDISPATCHID and WORKERID=:WORKERID;
INSERT INTO CONTAINERSTEPDISPATCHDETAIL(CONTAINERSTEPDISPATCHID,WORKERID,createdate) VALUES(:CONTAINERSTEPDISPATCHID,:WORKERID,sysdate); end;</string>.Value)
        If isZS Then
            strsql.Clear()
            strsql.AppendLine(<string> begin delete CONTAINERSTEPDISPATCHDETAIL_ZS where CONTAINERSTEPDISPATCHID=:CONTAINERSTEPDISPATCHID and WORKERID=:WORKERID;
                                            INSERT INTO CONTAINERSTEPDISPATCHDETAIL_ZS(CONTAINERSTEPDISPATCHID,WORKERID) VALUES(:CONTAINERSTEPDISPATCHID,:WORKERID); end;
                              </string>.Value)
        End If
        'modify 2017/10/10
        Dim newpar(1) As OracleParameter
        newpar(0) = DatabaseHelper.NewParameter(":CONTAINERSTEPDISPATCHID", ContainerStepDispatchID, OracleType.VarChar)
        newpar(1) = DatabaseHelper.NewParameter(":WORKERID", Workerid, OracleType.VarChar)
        Return New Object() {strsql.ToString, newpar}

    End Function

    ''' <summary>
    ''' 根据派工ID获取派工详细信息 add tianFW 2017-08-08
    ''' </summary>
    ''' <param name="ContainerStepDispatchID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetDispatchdetails(ByVal ContainerStepDispatchID As String, Optional ByVal isZS As Boolean = False) As DataTable Implements IBusiness.uLMSDispatchIBusiness.GetDispatchdetails
        Dim strsql As String = "SELECT C.*,T.TEAMID,T.TEAMNAME,EM.FULLNAME WORKERNAME FROM CONTAINERSTEPDISPATCHDETAIL C "
        If isZS Then
            strsql = "SELECT C.*,T.TEAMID,T.TEAMNAME,EM.FULLNAME WORKERNAME FROM CONTAINERSTEPDISPATCHDETAIL_ZS C "
        End If
        strsql += " LEFT JOIN TEAMEMPLOYEE TEM ON TEM.EMPLOYEEID = C.WORKERID "
        strsql += " LEFT JOIN TEAM T ON T.TEAMID = TEM.TEAMID "
        strsql += " LEFT JOIN EMPLOYEE EM ON EM.EMPLOYEEID = C.WORKERID "
        strsql += String.Format(" WHERE C.CONTAINERSTEPDISPATCHID = '{0}'", ContainerStepDispatchID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 删除派工详细信息dd tianFW 2017-08-08
    ''' </summary>
    ''' <param name="ContainerStepDispatchID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DelDispatchdetails(ByVal ContainerStepDispatchID As String, Optional ByVal isZS As Boolean = False) As Boolean Implements IBusiness.uLMSDispatchIBusiness.DelDispatchdetails
        Dim strsql As String = String.Format("DELETE FROM CONTAINERSTEPDISPATCHDETAIL C WHERE C.CONTAINERSTEPDISPATCHID = '{0}'", ContainerStepDispatchID)
        If isZS Then
            strsql = String.Format("DELETE FROM CONTAINERSTEPDISPATCHDETAIL_ZS C WHERE C.CONTAINERSTEPDISPATCHID = '{0}'", ContainerStepDispatchID)
        End If
        If DatabaseHelper.ExecuteSql(strsql) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 根据ContainerID，工序id，员工id获取派工信息 create tianFW
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetDispatchingWoker(ByVal para As Dictionary(Of String, String)) As Integer Implements IBusiness.uLMSDispatchIBusiness.GetDispatchingWoker
        Dim strsql As String = String.Format("SELECT * FROM CONTAINERSTEPDISPATCH CSD WHERE CSD.ISUSE=0 AND CSD.CONTAINERID = '{0}'", para("ContainerID"))
        strsql += String.Format(" AND CSD.WORKFLOWSTEPID = '{0}'", para("WorkflowStepID"))
        If para.Keys.Contains("WorkerID") Then
            strsql += String.Format(" AND CSD.WORKERID = '{0}'", para("WorkerID"))
        End If
        Return DatabaseHelper.ExecuteSql(strsql)
    End Function

    ''' <summary>
    ''' 根据ContainerID，员工id获取派工信息 create tianFW
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetHistoryDispatching(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSDispatchIBusiness.GetHistoryDispatching
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT '' CONTAINERNAME,CSD.*,RE.DESCRIPTION || '(' || RE.RESOURCENAME || ')' RESOURCENAME,EM.FULLNAME AS DISPATCHERNAME,")
        strsql.AppendLine("EM2.FULLNAME AS WORKERNAME,T.TEAMNAME,CSD.CREATEDATE TXNDATE,A.CONTAINERSTEPDISPATCHID REPORT_CONTAINERSTEPDISPATCHID")
        strsql.AppendLine("FROM CONTAINERSTEPDISPATCH CSD ")
        strsql.AppendLine("LEFT JOIN (")
        strsql.AppendLine("SELECT CSW.CONTAINERSTEPDISPATCHID  FROM CONTAINERSTEPWORKREPORT CSW")
        strsql.AppendFormat("WHERE  CSW.WORKFLOWSTEPID='{0}' AND CSW.ISUSE=0 AND", para("WorkflowStepID"))
        If Not para.Keys.Contains("ContainerIDs") Then
            strsql.AppendFormat("  CSW.CONTAINERID = '{0}'", para("ContainerID"))
        Else
            strsql.AppendFormat("  CSW.CONTAINERID IN ({0})", para("ContainerIDs"))
        End If
        strsql.AppendLine(" GROUP BY CSW.CONTAINERSTEPDISPATCHID")
        strsql.AppendLine(") A ON A.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID")
        strsql.AppendLine("LEFT JOIN RESOURCEDEF RE ON RE.RESOURCEID = CSD.RESOURCEID ")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM ON EM.EMPLOYEEID = CSD.EMPLOYEEID")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM2 ON EM2.EMPLOYEEID = CSD.WORKERID ")
        strsql.AppendLine("LEFT JOIN TEAM T ON T.TEAMID = CSD.TEAMID")
        strsql.AppendFormat(" WHERE CSD.ISUSE=0 AND  CSD.WORKFLOWSTEPID = '{0}' AND", para("WorkflowStepID"))
        If para.Keys.Contains("ContainerIDs") Then
            strsql.AppendFormat("  CSD.CONTAINERID IN ({0})", para("ContainerIDs"))
        Else
            strsql.AppendFormat("  CSD.CONTAINERID = '{0}'", para("ContainerID"))
        End If
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
    End Function

    ''' <summary>
    ''' 保存生产准备程序准备
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveOrderPrepare(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSDispatchIBusiness.SaveOrderPrepare
        Dim strSql As String = <string>
                                  INSERT INTO ncarrangements (containerid,createdate,employeeid,isintegration,istransfer,ncarrangementsid,needdate,resourceid,teamid,workflowstepid)
                                  VALUES(:containerid,SYSDATE,:employeeid,0,0,:ncarrangementsid,to_date(:needdate,'yyyy-mm-dd hh24:mi:ss'),:resourceid,:teamid,:workflowstepid)
                              </string>

        Dim insertParam(6) As OracleParameter

        insertParam(0) = DatabaseHelper.NewParameter(":containerid", para("ContainerID"), OracleType.VarChar)
        insertParam(1) = DatabaseHelper.NewParameter(":employeeid", para("EmployeeID"), OracleType.VarChar)
        insertParam(2) = DatabaseHelper.NewParameter(":ncarrangementsid", Guid.NewGuid.ToString, OracleType.VarChar)
        insertParam(3) = DatabaseHelper.NewParameter(":needdate", para("NeedDate"), OracleType.VarChar)
        insertParam(4) = DatabaseHelper.NewParameter(":resourceid", para("ResourceID"), OracleType.VarChar)
        insertParam(5) = DatabaseHelper.NewParameter(":teamid", para("TeamID"), OracleType.VarChar)
        insertParam(6) = DatabaseHelper.NewParameter(":workflowstepid", para("WorkFlowStepID"), OracleType.VarChar)

        Dim i As Integer
        i = DatabaseHelper.ExecuteSql(strSql, insertParam)
        If i > 0 Then
            SaveOrderPrepare = True
        Else
            SaveOrderPrepare = False
        End If
    End Function

    ''' <summary>
    ''' 获取已派工员工信息 create tianFW 
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetDispatchWorkerList(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetDispatchWorkerList
        Dim strsql As New StringBuilder
        Dim strsql_ As String =
            <string>
                SELECT CSD.CONTAINERSTEPDISPATCHID,
						CSD.CONTAINERID,
						CSD.WORKFLOWSTEPID,
						CSD.WORKERID                CSD_WORKERID,
						CSDT.WORKERID               CSDT_WORKERID
			    FROM CONTAINERSTEPDISPATCH CSD
			    LEFT JOIN CONTAINERSTEPDISPATCHDETAIL CSDT ON CSD.CONTAINERSTEPDISPATCHID = CSDT.CONTAINERSTEPDISPATCHID	
				LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSD.WORKFLOWSTEPID
				LEFT JOIN WORKFLOW WF ON WF.WORKFLOWID =WS.WORKFLOWID		
            </string>
        strsql.AppendLine(strsql_)
        strsql.AppendFormat(" WHERE CSD.ISUSE=0 ")
        If para.Keys.Contains("ContainerIDs") = True Then
            strsql.AppendFormat(" AND CSD.CONTAINERID IN ({0})", para("ContainerIDs"))
        End If
        If para.Keys.Contains("WorkflowID") = True Then
            strsql.AppendFormat(" AND WF.WORKFLOWID='{0}'", para("WorkflowID"))
        End If
        If para.Keys.Contains("Sequence") = True Then
            strsql.AppendFormat(" AND WS.SEQUENCE>={0}", para("Sequence"))
        End If
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 获取员工的未报工的派工数和总工时 create tianFW
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkerDonotWorkReportQtyAndTotalUnit(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetWorkerDonotWorkReportQtyAndTotalUnit
        Dim strsql As String =
            <string>
                SELECT CSD.WORKERID CSD_WORKERID,CSDT.WORKERID CSDT_WORKERID, CSD.QTY, NVL(A.REPORTQTY, 0) REPORTQTY,
			    ROUND((CSD.QTY - NVL(A.REPORTQTY, 0)) / NVL(B.COUNT_, 1), 2) DONOTREPORTQTY,
			    ROUND((CSD.QTY - NVL(A.REPORTQTY, 0)) * NVL(ST.DURATIONPERUNIT, 0) / NVL(B.COUNT_, 1), 2) TOTALDUARUNIT,
			    NVL(ST.DURATIONPERUNIT, 0) DURATIONPERUNIT
	            FROM CONTAINERSTEPDISPATCH CSD
	            LEFT JOIN CONTAINERSTEPDISPATCHDETAIL CSDT
		        ON CSD.CONTAINERSTEPDISPATCHID = CSDT.CONTAINERSTEPDISPATCHID
                --获取已经报工的数量
	            LEFT JOIN (SELECT CSW.CONTAINERSTEPDISPATCHID, SUM(CSW.QTY) REPORTQTY
							 FROM CONTAINERSTEPWORKREPORT CSW WHERE CSW.ISUSE = 0
							GROUP BY CSW.CONTAINERSTEPDISPATCHID) A
		        ON A.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID
	            LEFT JOIN STEPSCHEDULINGDETAIL ST ON ST.STEPID = CSD.WORKFLOWSTEPID
                --获取多人派工派给几个人
	            LEFT JOIN (SELECT CSDT_.CONTAINERSTEPDISPATCHID, COUNT(*) COUNT_
							 FROM CONTAINERSTEPDISPATCHDETAIL CSDT_
							GROUP BY CSDT_.CONTAINERSTEPDISPATCHID) B
		        ON CSD.CONTAINERSTEPDISPATCHID = B.CONTAINERSTEPDISPATCHID
	            LEFT JOIN CONTAINERCURRENTINFO CCO
		        ON CSD.CONTAINERID = CCO.CONTAINERID
                WHERE (CSD.WORKFLOWSTEPID = CCO.WORKFLOWSTEPID
		        OR (CSD.WORKFLOWSTEPID != CCO.WORKFLOWSTEPID AND  CSD.WORKFLOWSTEPID NOT IN
			    (SELECT DISTINCT (CSW_.CONTAINERSTEPDISPATCHID) FROM CONTAINERSTEPWORKREPORT CSW_ WHERE CSW_.ISUSE = 0)))
            </string>
        strsql += String.Format(" AND CSD.FACTORYID='{0}' AND CSD.WORKCENTERID='{1}'", para("FactoryID"), para("WorkCenterID"))
        If para.Keys.Contains("TeamID") Then
            strsql += String.Format(" AND CSD.TEAMID ='{0}'", para("TeamID"))
        End If
        strsql += " AND CCO.CONTAINERSTATUS=1 AND CSD.ISUSE=0"
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 获取批次工序班组派工信息
    ''' create:Wangjh
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepTeamDispatch(ByVal para As Dictionary(Of String, String)) As DataSet Implements uLMSDispatchIBusiness.GetStepTeamDispatch

        Dim strSql As String = <string>
                                 SELECT DISTINCT  WS.WORKFLOWSTEPID,WS.WORKFLOWSTEPNAME,CTDWS.containername,CTDWS.teamname,CTDWS.workcentername,CTDWS.fullname,
                                CTDWS.containerid,CTDWS.teamid,CTDWS.workcenterid,CTDWS.workflowstepid,CTDWS.CONTAINERTYPE,CTDWS.containerteamdispatchid,
                                CTDWS.togetherflag,ws.sequence,T.TEAMNAME BASETEAMNAME,DECODE(CTDWS.CONTAINERTYPE,7,NULL,NVL2(CSD.CONTAINERSTEPDISPATCHID,'是','否')) DISPATCHNUM  FROM WORKFLOWSTEP WS 
                                LEFT JOIN (
                                            SELECT DISTINCT WS.WORKFLOWSTEPNAME,DECODE(c.containertype,7,NULL, C.containername) containername,t.teamname,wc.workcentername,
							                e.fullname,decode(c.containertype,7,NULL,ctd.containerid) containerid,ctd.teamid,ctd.workcenterid,ctd.workflowstepid,
							                C.CONTAINERTYPE,decode(c.containertype,7,NULL,ctd.containerteamdispatchid) containerteamdispatchid,c.togetherflag,WS.SEQUENCE,T2.TEAMNAME BASETEAMNAME 
                                            FROM    containerteamdispatch ctd
							                LEFT JOIN container c ON c.containerid=ctd.containerid
							                LEFT JOIN team t ON t.teamid=ctd.teamid
							                LEFT JOIN Workcenter wc ON wc.workcenterid=ctd.workcenterid
							                LEFT JOIN employee e ON e.employeeid=ctd.employeeid
							                LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID=CTD.WORKFLOWSTEPID 
                                            LEFT JOIN TEAM T2 ON T2.TEAMID=WS.TEAMID
                               </string>
        strSql += String.Format(" WHERE ctd.containerid in ({0})", para("ContainerID"))
        strSql += String.Format("  AND CTD.FACTORYID='{0}' AND ctd.isuse=0", para("FactoryID"))
        strSql += "  ) CTDWS ON  CTDWS.workflowstepid=ws.workflowstepid"
        strSql += " LEFT JOIN TEAM T ON T.TEAMID=WS.TEAMID"
        strSql += String.Format(" LEFT JOIN CONTAINERSTEPDISPATCH CSD ON CSD.CONTAINERID IN ({0}) AND CSD.WORKFLOWSTEPID=WS.WORKFLOWSTEPID AND CSD.ISUSE=0", para("ContainerID")) '关联派工信息
        strSql += String.Format(" WHERE WS.WORKFLOWID='{0}' AND WS.SEQUENCE>={1} AND (WS.workcenterid IS NULL OR  WS.workcenterid='{2}')", para("WorkFlowID"), para("Sequence"), para("WorkCenterID"))
        If para.ContainsKey("WorkFlowStepID") Then
            strSql += String.Format(" AND ws.workflowstepid='{0}'  ", para("WorkFlowStepID"))
        End If
        strSql += " ORDER BY CTDWS.togetherflag,WS.SEQUENCE"
        Dim ds As New DataSet
        Dim sumDt As New DataTable
        sumDt = DatabaseHelper.Query_ReturnDatTable(strSql)

        '子表 单件单卡
        Dim tempDt As DataTable = sumDt.Copy
        Dim dv As DataView = tempDt.DefaultView
        dv.RowFilter = "containertype='7'"

        Dim QueryCondition As String = ""

        For Each drv As DataRowView In dv
            QueryCondition += String.Format("'{0}',", drv("WORKFLOWSTEPID"))
        Next
        If QueryCondition.Length > 0 Then
            QueryCondition = QueryCondition.Substring(0, QueryCondition.Length - 1)
            QueryCondition = " AND CTD.WORKFLOWSTEPID IN (" + QueryCondition + ")"
            QueryCondition += " AND c.containerid IN  (" + para("SingleContainerID") + ")"
        End If
        'add by tianFW csd.isuse = 0 2018年9月29日
        Dim sonSql As String = <string>
                                    SELECT DISTINCT c.containername,ctd.containerteamdispatchid,c.containerid,ctd.workflowstepid,c.togetherflag,C.CONTAINERTYPE,T.TEAMNAME,T.TEAMID,CTD.WORKCENTERID, 
                                    NVL2(csd.containerstepdispatchid,'是','否') DISPATCHNUM FROM containerteamdispatch ctd
                                    LEFT JOIN container c ON c.containerid=ctd.containerid
                                    LEFT JOIN TEAM T ON T.TEAMID=CTD.TEAMID
                                    LEFT JOIN containerstepdispatch csd ON csd.containerid=ctd.containerid AND csd.workflowstepid=ctd.workflowstepid and csd.isuse = 0
                                    WHERE c.containertype=7 AND ctd.isuse=0
                               </string>
        sonSql += String.Format(" AND ctd.factoryid='{0}'", para("FactoryID"))
        If Not String.IsNullOrWhiteSpace(QueryCondition) Then
            sonSql += String.Format(" {0}", QueryCondition)
        End If
        sonSql += " ORDER BY c.containername"
        Dim sonContainerDt As New DataTable
        If QueryCondition.Length > 0 Then '存在单件单卡的时候才查询子表
            sonContainerDt = DatabaseHelper.Query_ReturnDatTable(sonSql)
        Else
            sonContainerDt = tempDt.Clone
        End If

        ds.Tables.Add(sumDt)
        ds.Tables(0).TableName = "sumContainer"
        ds.Tables.Add(sonContainerDt)
        ds.Tables(1).TableName = "sonContainer"
        If QueryCondition.Length > 0 Then '存在单件单卡的时候才关联
            ds.Relations.Add("SumContainer",
           New DataColumn() {ds.Tables(0).Columns("TOGETHERFLAG"), ds.Tables(0).Columns("CONTAINERTYPE"), ds.Tables(0).Columns("WORKFLOWSTEPID"), ds.Tables(0).Columns("TEAMID"), ds.Tables(0).Columns("WORKCENTERID")},
           New DataColumn() {ds.Tables(1).Columns("TOGETHERFLAG"), ds.Tables(1).Columns("CONTAINERTYPE"), ds.Tables(1).Columns("WORKFLOWSTEPID"), ds.Tables(1).Columns("TEAMID"), ds.Tables(1).Columns("WORKCENTERID")})
        End If

        GetStepTeamDispatch = ds
    End Function

    ''' <summary>
    ''' 获取数控工序信息 create tianFW 2017/10/16
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSKGXInfo(ByVal stepID As String, ByVal type As String) As DataTable Implements uLMSDispatchIBusiness.GetSKGXInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine(" SELECT WS.WORKFLOWSTEPID,WS.WORKFLOWSTEPNAME,S.SPECID,S.NCCODE,S.NCNUMBER,S.NCREVISION,ST.SPECTYPENAME")
        strsql.AppendLine("FROM WORKFLOWSTEP WS")
        strsql.AppendLine("LEFT JOIN SPECBASE SB ON SB.SPECBASEID = WS.SPECBASEID")
        strsql.AppendLine("LEFT JOIN SPEC S ON S.SPECID = NVL(SB.REVOFRCDID, WS.SPECID)")
        strsql.AppendLine("LEFT JOIN SPECTYPE ST ON ST.SPECTYPEID = S.SPECTYPEID ")
        strsql.AppendFormat("WHERE WS.sequence IS NOT NULL AND WS.WORKFLOWSTEPID IN ({0})", stepID)
        strsql.AppendFormat(" AND ST.SPECTYPENAME='{0}'", type) 'modify:wangjh 2019/02/19 SPCTYPENAME->SPECTYPENAME
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 根据WorkFlowID获取已经存在派工的工序
    ''' create:Wangjh 2017-10-16
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetDispathStepByWorkflowID(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetDispathStepByWorkflowID
        Dim strSql As String = <string>
                                    SELECT ws.workflowstepid,ws.workflowstepname,A.resourceid,A.RESOURCENAME FROM workflowstep ws 
                                    LEFT JOIN workflow w ON w.workflowid=ws.workflowid
                                    LEFT JOIN (
                                              SELECT csd.workflowstepid,csd.resourceid,rd.resourcename FROM containerstepdispatch csd
                                              LEFT JOIN Resourcedef rd ON rd.resourceid=csd.resourceid
                               </string>
        strSql += String.Format(" WHERE csd.containerid IN ({0}) AND csd.isuse=0 AND csd.factoryid='{1}'", para("ContainerIDs"), para("FactoryID"))
        If para.ContainsKey("WorkerID") Then 'add YangJiang 20180412
            strSql += String.Format(" AND csd.workerid='{0}'", para("WorkerID"))
        End If
        If para.ContainsKey("TeamID") Then 'add YangJiang 20180412
            strSql += String.Format(" AND csd.teamid='{0}'", para("TeamID"))
        End If
        strSql += " GROUP BY csd.workflowstepid,csd.resourceid,rd.resourcename ) A ON A.workflowstepid=WS.WORKFLOWSTEPID"
        strSql += String.Format(" WHERE w.workflowid='{0}'", para("WorkFlowID"))
        If para.ContainsKey("Sequence") Then
            strSql += String.Format(" AND ws.sequence>={0}", para("Sequence"))
        End If
        strSql += " AND A.workflowstepid IS NOT NULL"
        strSql += " order by ws.sequence"
        GetDispathStepByWorkflowID = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 查询生产准备
    ''' create:Wangjh 2017-10-16
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetPreparationStepByWorkflowID(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetPreparationStepByWorkflowID
        Dim strSql As String = <string>
                                    SELECT ws.sequence,ws.workflowstepid,ws.workflowstepname,A.resourceid,A.RESOURCENAME FROM workflowstep ws 
                                    LEFT JOIN workflow w ON w.workflowid=ws.workflowid
                                 LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                                 LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
                                    LEFT JOIN (
                                              SELECT csd.workflowstepid,csd.resourceid,rd.resourcename FROM containerstepdispatch csd
                                              LEFT JOIN Resourcedef rd ON rd.resourceid=csd.resourceid
                               </string>

        strSql += " left join containerstepdispatchdetail csdd on csdd.containerstepdispatchid = csd.containerstepdispatchid "

        strSql += String.Format(" WHERE csd.containerid IN ({0}) AND csd.isuse=0 AND csd.factoryid='{1}'", para("ContainerIDs"), para("FactoryID"))

        If para.ContainsKey("WorkerID") Then 'add YangJiang 20180412
            strSql += String.Format(" AND NVL(csd.workerid, csdd.workerid)='{0}'", para("WorkerID"))
        End If
        If para.ContainsKey("TeamID") Then 'add YangJiang 20180412
            strSql += String.Format(" AND csd.teamid='{0}'", para("TeamID"))
        End If
        strSql += " GROUP BY csd.workflowstepid,csd.resourceid,rd.resourcename ) A ON A.workflowstepid=WS.WORKFLOWSTEPID"
        strSql += String.Format(" WHERE w.workflowid='{0}'", para("WorkFlowID"))
        If para.ContainsKey("Sequence") Then
            strSql += String.Format(" AND ws.sequence>={0}", para("Sequence"))
        End If
        strSql += " AND (A.workflowstepid IS NOT NULL or s.lastcheck=1)"
        strSql += " group by ws.sequence,ws.workflowstepid,ws.workflowstepname,A.resourceid,A.RESOURCENAME "
        strSql += " order by ws.sequence"
        GetPreparationStepByWorkflowID = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 装试组派工 create tianFW 2017年11月24日
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ZSTeamDispatch(ByVal dto As ContainerTeamDispatchDTO) As Boolean Implements uLMSDispatchIBusiness.ZSTeamDispatch
        Dim strsql As String = String.Format("UPDATE CONTAINERTEAMDISPATCH CTD SET CTD.TEAMID='{0}',ToTeamDispatchDate=sysdate,CTD.ToTeamDispatcherId='{1}', CTD.ISTRANSFER=0 WHERE CTD.CONTAINERTEAMDISPATCHID='{2}'", dto.TeamID, dto.ToTeamDispatcherId, dto.ContainerTeamDispatchID)
        If DatabaseHelper.ExecuteSql(strsql) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 获取员工的派工和报工信息(已报完则不显示) create tianFW 
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkerDisAndReportInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSDispatchIBusiness.GetWorkerDisAndReportInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT EM.FULLNAME DISPATCHERNAME,EM1.FULLNAME WORKERNAME,NVL(B.COUNT_,1) DISWORKERCOUNT,CSD.QTY DISPATCHQTY,NVL(A.REPORTQTY, 0) REPORTQTY,CSD.CREATEDATE,")
        strsql.AppendLine("CO.CONTAINERNAME,CO.CONTAINERID,WS.WORKFLOWSTEPNAME,CO.QTY,EM2.FULLNAME WORKERNAME_")
        strsql.AppendLine("FROM CONTAINERSTEPDISPATCH CSD")
        strsql.AppendLine("LEFT JOIN CONTAINERCURRENTINFO CO ON CO.CONTAINERID = CSD.CONTAINERID")
        strsql.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID=CSD.WORKFLOWSTEPID")
        strsql.AppendLine("LEFT JOIN CONTAINERSTEPDISPATCHDETAIL CSDD ON CSDD.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM ON EM.EMPLOYEEID = CSD.EMPLOYEEID")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM1 ON EM1.EMPLOYEEID = CSD.WORKERID")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM2 ON EM2.EMPLOYEEID = CSDD.WORKERID")
        strsql.AppendLine("LEFT JOIN (SELECT CSDT_.CONTAINERSTEPDISPATCHID, COUNT(*) COUNT_")
        strsql.AppendLine("FROM CONTAINERSTEPDISPATCHDETAIL CSDT_")
        strsql.AppendLine("GROUP BY CSDT_.CONTAINERSTEPDISPATCHID) B ON CSD.CONTAINERSTEPDISPATCHID = B.CONTAINERSTEPDISPATCHID")
        strsql.AppendLine("LEFT JOIN (SELECT SUM(CSW.QTY) REPORTQTY, CSW.CONTAINERSTEPDISPATCHID FROM CONTAINERSTEPWORKREPORT CSW")
        strsql.AppendLine("WHERE CSW.ISUSE = 0 GROUP BY CSW.CONTAINERSTEPDISPATCHID) A")
        strsql.AppendLine("ON A.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID")
        strsql.AppendFormat("WHERE CSD.ISUSE = 0 AND NVL(CSD.WORKERID, CSDD.WORKERID) = '{0}' AND CSD.QTY > NVL(A.REPORTQTY, 0) AND WS.SEQUENCE>=CO.WORKFLOWSTEPSEQUENCE", para("WorkerID"))
        strsql.AppendLine(" AND CO.CONTAINERSTATUS=1 AND CO.ISINSTORE=0 ORDER BY CSD.CONTAINERID,WS.SEQUENCE")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strsql.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取同件号同工序的上一次派工信息
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetLastDispatchInfoofProductAndSpet(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetLastDispatchInfoofProductAndSpet
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT * FROM CONTAINERSTEPDISPATCH CSD ")
        strsql.AppendLine("LEFT JOIN CONTAINER C ON C.CONTAINERID = CSD.CONTAINERID")
        strsql.AppendLine("LEFT JOIN PRODUCT P ON P.PRODUCTID = C.PRODUCTID")
        strsql.AppendLine("LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = P.PRODUCTBASEID")
        strsql.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSD.WORKFLOWSTEPID")
        strsql.AppendLine("WHERE CSD.ISUSE = 0 And C.STATUS = 1")
        strsql.AppendFormat(" AND PB.PRODUCTNAME ='{0}' AND WS.WORKFLOWSTEPNAME='{1}'", para("ProductName"), para("WorkflowStepName"))
        strsql.AppendFormat("AND CSD.CONTAINERID<>'{0}' ORDER BY CSD.CREATEDATE DESC", para("ContainerID"))
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 获取班组派工页面的查询资料 Add by Yuxj20180126
    ''' 取消对发动机件号的查询Modify by Yuxj2018年5月22日
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetShiftTeamSearchData(ByVal para As Dictionary(Of String, String)) As DataSet Implements uLMSDispatchIBusiness.GetShiftTeamSearchData
        If Not para.ContainsKey("Role") Then
            GetShiftTeamSearchData = New DataSet
            Exit Function
        End If

        Dim ds As New DataSet
        Dim sb As New StringBuilder
        sb.AppendLine(" SELECT c.containername,c.containerid,m.mfgordername,u.attributevalue machinename,p.description,")
        sb.AppendLine("pf.productfamilyname,pb.productname,c.togetherflag,csp.ContainerTeamDispatchID,")
        sb.AppendLine("pf.productfamilyname pfname")
        'Modify by Yuxj 20180202 获取当前阶段资料并依此过滤
        sb.AppendLine(",wfs.workflowstepname ,wfs.workflowstepid, wfs1.workflowstepname machineStepName,wfs1.workflowstepid machineStepId,")
        sb.AppendLine(" m.plannedstartdate,m.plannedcompletiondate,st.shiftname,t.teamname")
        sb.AppendLine(" FROM container c ")
        sb.AppendLine("LEFT JOIN mfgorder m ON c.mfgorderid=m.mfgorderid ")
        sb.AppendLine("LEFT JOIN Userattribute u ON u.parentid=m.mfgorderid  ")
        sb.AppendLine(" LEFT JOIN product p ON c.productid=p.productid ")
        'sb.AppendLine(" LEFT JOIN Factory f ON p.factoryid=f.factoryid ")
        sb.AppendLine(" LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid")
        sb.AppendLine(" LEFT JOIN productfamily pf ON p.productfamilyid=pf.productfamilyid ")

        'Modify by Yuxj 20180130
        sb.AppendLine("LEFT JOIN CONTAINERTEAMDISPATCH csp ON c.containerid=csp.containerid ")

        'Modify by Yuxj 20180202
        sb.AppendLine(" LEFT JOIN Currentstatus CS ON c.currentstatusid=cs.Currentstatusid ")
        sb.AppendLine(" LEFT JOIN workflowstep wfs ON cs.workflowstepid=wfs.workflowstepid")
        sb.AppendLine(" LEFT JOIN enginepartplan ep ON c.containerid=ep.containerid")

        'Modify by Yuxj20180702 修改厂别的查询方式
        sb.AppendLine(" LEFT JOIN workflow wf ON wf.workflowid=ep.workflowid ")
        sb.AppendLine(" LEFT JOIN Factory f ON wf.factoryid=f.factoryid ")

        sb.AppendLine(" LEFT JOIN workflowstep wfs1 ON ep.workflowstepid=wfs1.workflowstepid ")
        'add by tianFW 2018年5月2日 添加对班和组的查询
        sb.AppendLine(" LEFT JOIN shift st on st.shiftid = csp.shiftid")
        sb.AppendLine(" LEFT JOIN team t on t.teamid = csp.teamid")
        sb.AppendLine("where c.status='1' and u.userattributename='发动机' ") 'AND im.containerno IS NOT NULL
        If para.ContainsKey("MfgOrderName") Then
            sb.AppendFormat("and m.MfgOrderName='{0}' ", para("MfgOrderName"))
        End If

        If para.ContainsKey("ContainerNo") Then
            sb.AppendFormat("and c.togetherflag='{0}' ", para("ContainerNo"))
        End If

        If para.ContainsKey("Factoryid") Then
            sb.AppendFormat("and f.Factoryid='{0}' ", para("Factoryid"))
        End If

        If para.ContainsKey("MachineStart") Then
            If para.ContainsKey("MachineEnd") Then
                sb.AppendFormat("and u.attributevalue between  '{0}' and '{1}' ", para("MachineStart"), para("MachineEnd"))
            Else
                sb.AppendFormat("and u.attributevalue='{0}' ", para("MachineStart"))
            End If
        End If

        If para.ContainsKey("MachineStatus") Then
            sb.AppendFormat(" AND wfs1.workflowstepid='{0}' ", para("MachineStatus"))
        End If

        If para.ContainsKey("ProductName") Then 'add by tianFW 2018年5月22日
            sb.AppendFormat("and (pb1.productname LIKE '%{0}%' or pb1.productname LIKE '%{0}%')", para("ProductName"))
        End If

        If para("Role") = "Shift" Then
            If para("ContainerStatus") = "1" Then
                sb.AppendLine(" AND csp.shiftid IS not NULL ") 'AND csp.teamid IS NULL 
            Else
                sb.AppendLine(" AND c.containerid not in (select distinct containerid from CONTAINERTEAMDISPATCH) ")
            End If
        Else
            If para("ContainerStatus") = "1" Then
                sb.AppendLine(" AND csp.teamid IS not NULL ")
            ElseIf para("ContainerStatus") = "0" Then
                sb.AppendLine(" AND csp.teamid IS NULL AND csp.shiftid IS not NULL ")
            End If
        End If

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Dim dv As DataView = dt.DefaultView
        Dim dtContainerNo As DataTable = dv.ToTable("ContainerNo", True, "machinename", "togetherflag", "containername", "productname", "productfamilyname", "description",
                                                    "ContainerId", "ContainerTeamDispatchID", "plannedstartdate", "plannedcompletiondate", "workflowstepname",
                                                    "workflowstepid", "machineStepName", "machineStepId", "shiftname", "teamname")
        ds.Tables.Add(dtContainerNo)
        GetShiftTeamSearchData = ds
    End Function

    ''' <summary>
    ''' 获取装试发动机工艺路线 Yuxj20180201
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetMachineStepList(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetMachineStepList
        Dim strsql As String =
            <string>
                SELECT wfs.workflowid,wfs.workflowstepid,wfs.workflowstepname,wfs.sequence
                FROM mfgorder m
                INNER JOIN  enginepartplan ep ON m.mfgorderid=ep.mfgorderid
                LEFT JOIN Userattribute u ON m.mfgorderid=u.parentid
                LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid
                WHERE u.userattributename='发动机'
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        If para.ContainsKey("MfgOrderName") Then
            sb.AppendFormat(" and m.mfgordername='{0}'", para("MfgOrderName"))
        End If

        If para.ContainsKey("ContainerNo") Then
            sb.AppendFormat(" and ep.ContainerNo='{0}'", para("ContainerNo"))
        End If

        If para.ContainsKey("MachineStart") Then
            If para.ContainsKey("MachineEnd") Then
                sb.AppendFormat(" and u.attributevalue between '{0}' and '{1}'", para("MachineStart"), para("MachineEnd"))
            Else
                sb.AppendFormat(" and u.attributevalue like '%{0}%'", para("MachineStart"))
            End If
        End If
        sb.AppendLine("ORDER BY wfs.sequence asc ")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Dim dv As DataView = dt.DefaultView
        dt = dv.ToTable(True, "workflowstepid", "workflowstepname")
        Return dt
    End Function

    ''' <summary>
    ''' 获取已接收的工区协作信息 create tianFW 2018年4月9日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkCenterInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetWorkCenterInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT C.CONTAINERID,C.CONTAINERNAME,C.WORKFLOWSTEPID,C.WORKFLOWSTEPNAME,EM.FULLNAME EMPLOYEENAME,EM2.FULLNAME REVEMPLOYEENAME,")
        strsql.AppendLine("CWCD.CREATEDATE, CWCD.RECVDATE, CWCD.CONTAINERWORKCENTERDISPATCHID,WC.WORKCENTERNAME,CWCD.CREATEDATE TXNDATE")
        strsql.AppendLine("FROM CONTAINERCURRENTINFO C ")
        strsql.AppendLine("LEFT JOIN CONTAINERWORKCENTERDISPATCH CWCD ON CWCD.CONTAINERID = C.CONTAINERID AND C.WORKFLOWSTEPID = CWCD.WORKFLOWSTEPID")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM ON EM.EMPLOYEEID = CWCD.EMPLOYEEID")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM2 ON EM2.EMPLOYEEID = CWCD.RECVEMPLOYEEID")
        strsql.AppendLine("LEFT JOIN WORKCENTER WC ON WC.WORKCENTERID = CWCD.TOWORKCENTERID")
        strsql.AppendLine("WHERE CWCD.ISUSE=0 AND CWCD.RECVEMPLOYEEID IS NOT NULL")
        If para.Keys.Contains("ContainerIDs") Then
            strsql.AppendFormat(" AND C.CONTAINERID IN ({0}) AND C.WORKFLOWSTEPID='{1}'", para("ContainerIDs"), para("WorkflowStepID"))
        Else
            strsql.AppendFormat(" AND C.CONTAINERID='{0}' AND C.WORKFLOWSTEPID='{1}'", para("ContainerID"), para("WorkflowStepID"))
        End If
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 撤销工区协作 create tianFW 2018年4月9日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function ReverseWorkCenterInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSDispatchIBusiness.ReverseWorkCenterInfo
        ReverseWorkCenterInfo = False
        Dim strsql As New StringBuilder
        strsql.AppendFormat("UPDATE CONTAINERWORKCENTERDISPATCH WCD SET WCD.ISUSE=1 WHERE WCD.CONTAINERWORKCENTERDISPATCHID='{0}'", para("ContainerWorkCenterDispatchID"))
        If DatabaseHelper.ExecuteSql(strsql.ToString()) > 0 Then
            strsql.Clear()
            strsql.AppendFormat(" UPDATE CONTAINERCURRENTINFO CU SET CU.TOWORKCENTERID=NULL,CU.WORKCENTERDISPATCHNUM=0,CU.WORKCENTERDISPATCHID=NULL WHERE CU.CONTAINERID='{0}'", para("ContainerID"))
            If DatabaseHelper.ExecuteSql(strsql.ToString()) > 0 Then
                Return True
            End If
        End If
    End Function

    ''' <summary>
    ''' 获取没有检验记录的工序没有报工的派工记录 create tianFW 2018年4月18日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetDispatchIDOfNotWorkReportAndStepNoCheckInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetDispatchIDOfNotWorkReportAndStepNoCheckInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CSR.DISPATCHID FROM CONTAINERSTEPRECORD CSR")
        strsql.AppendLine("WHERE CSR.CONTAINERSTEPRECORDID IS NULL AND CSR.CHECKEMPLOYEEID IS NULL ")
        strsql.AppendLine("AND CSR.DISPATCHID IS NOT NULL AND CSR.USEFULSTATUS = 1 AND CSR.USECOMPLETED = 0")
        strsql.AppendFormat(" AND CSR.CONTAINERID = '{0}' AND CSR.WORKFLOWSTEPID <>'{1}'", para("ContainerID"), para("WorkflowStepID"))
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 获取批次已经完工的工序 create tianFW 2018年4月18日
    ''' </summary>
    ''' <param name="ContainerIDs"></param>
    ''' <param name="WorkflowID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetIsCompletedStep(ByVal ContainerIDs As String, ByVal WorkflowID As String) As DataTable Implements uLMSDispatchIBusiness.GetIsCompletedStep
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CSR.WORKFLOWSTEPID FROM CONTAINERSTEPRECORD CSR ")
        strsql.AppendFormat("WHERE CSR.USEFULSTATUS = 1 AND CSR.USECOMPLETED=1 AND CSR.CONTAINERID IN ({0})", ContainerIDs)
        strsql.AppendFormat(" AND CSR.WORKFLOWID='{0}'", WorkflowID)
        strsql.AppendLine("GROUP BY CSR.WORKFLOWSTEPID ")
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 获取已经工区协作但未加工完成的记录
    ''' add:Wangjh 20180418
    ''' modify by tianFW 2018年10月18日 将containername修改为containerno
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkcenterDispatchOfNoCompete(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetWorkcenterDispatchOfNoCompete
        Dim strSql As String = "select containerno,productname,workflowstepname,workcentername,CREATEDATE CREATEDATE,recvdate recvdate,sum(qty) qty " &
                                "from(SELECT cci.containerno,cci.productname,ws.workflowstepname,cci.qty,wc.workcentername," &
                                "to_char(cwd.createdate,'yyyy/MM/dd hh24:Mi') CREATEDATE,to_char(cwd.recvdate,'yyyy/MM/dd hh24:Mi') recvdate" &
                               " FROM containerworkcenterdispatch cwd" &
                               " LEFT JOIN containercurrentinfo cci ON cci.containerid=cwd.containerid" &
                               " inner JOIN workflowstep ws ON ws.workflowstepid=cwd.workflowstepid AND ws.workflowid=cci.workflowid" &
                               " LEFT JOIN workcenter wc ON wc.workcenterid=cwd.toworkcenterid" &
                               " LEFT JOIN containersteprecord csr ON csr.containerid=cwd.containerid AND csr.workflowstepid=ws.workflowstepid" &
                               " WHERE cwd.isuse=0 AND ((CSR.USECOMPLETED=0 AND CSR.USEFULSTATUS=1) OR CSR.USECOMPLETED IS NULL) AND cci.containerid IS NOT NULL"
        If para.ContainsKey("FactoryID") Then
            strSql += String.Format(" AND CWD.factoryid='{0}'", para("FactoryID"))
        End If
        If para.ContainsKey("WorkCenterID") Then
            strSql += String.Format(" AND CWD.WorkCenterID='{0}'", para("WorkCenterID"))
        End If
        strSql += ") group by containerno,productname,workflowstepname,workcentername,CREATEDATE,recvdate"
        GetWorkcenterDispatchOfNoCompete = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取已申请的工序工具情况 add YangJiang 20180419
    ''' </summary>
    ''' <param name="ContainerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepToolsInfoPrepara(ByVal ContainerID As String, ByVal WorkFlowstepID As String) As System.Data.DataTable Implements IBusiness.uLMSDispatchIBusiness.GetStepToolsInfoPrepara
        Dim strSQL As New StringBuilder
        strSQL.AppendLine(" SELECT cci.containername,wfs.workflowstepname,am.workflowstepid,am.qty,am.toolno TOOLNAME,am.toolname DESCRIPTION,am.toolstatus,")
        strSQL.AppendLine(" CASE WHEN ct.cuttoolid IS NOT NULL THEN '刀具' WHEN mt.measuretoolid IS NOT NULL THEN '量具' WHEN st.specialtoolid IS NOT NULL THEN '夹模具' ELSE '' END TypeName,")
        strSQL.AppendLine(" am.arrangementsid FROM arrangements am ")
        strSQL.AppendLine(" LEFT JOIN containercurrentinfo cci ON cci.containerid=am.containerid")
        strSQL.AppendLine(" LEFT JOIN Workflowstep wfs ON wfs.workflowstepid=am.workflowstepid")
        strSQL.AppendLine(" LEFT JOIN Workflowsteptools wfst ON wfst.workflowstepid=wfs.workflowstepid")
        strSQL.AppendLine(" LEFT JOIN cuttool ct ON ct.cuttoolid=wfst.tools AND ct.cuttoolname=am.toolno")
        strSQL.AppendLine(" LEFT JOIN measuretool mt ON mt.measuretoolid=wfst.tools AND mt.measuretoolname=am.toolno")
        strSQL.AppendLine(" LEFT JOIN specialtool st ON st.specialtoolid=wfst.tools AND st.specialtoolname=am.toolno")
        strSQL.AppendFormat(" WHERE am.containerid in ({0}) and am.workflowstepid in ({1})", ContainerID, WorkFlowstepID)
        strSQL.AppendLine(" AND (st.specialtoolid IS NOT NULL OR ct.cuttoolid IS NOT NULL OR mt.measuretoolid IS NOT NULL)")
        GetStepToolsInfoPrepara = DatabaseHelper.Query_ReturnDatTable(strSQL.ToString)
    End Function

    ''' <summary>
    ''' 更新工装工具状态
    ''' add:Wangjh 20180816
    ''' </summary>
    ''' <param name="id"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateArrangToolStatus(ByVal id As String) As Boolean Implements uLMSDispatchIBusiness.UpdateArrangToolStatus
        Dim strSql As String = "UPDATE arrangements a SET a.toolstatus='已配送' WHERE a.arrangementsid='" & id & "'"
        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql)
        If (i > 0) Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 获取主页显示生产准备数据 add YangJiang 20180420
    ''' modify by tianFW 2018年10月19日 将查询containername修改为containerno并且将单件单卡合并为一条记录
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerPreparationData1(ByVal QueryCondition As System.Collections.Generic.Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSDispatchIBusiness.GetContainerPreparationData1
        Dim strSQL As New StringBuilder
        strSQL.AppendLine(" SELECT cci.productname,cci.containerno,am.toolstatus,cci.workflowstepname FROM containercurrentinfo cci")
        strSQL.AppendLine(" INNER JOIN arrangements am ON am.containerid=cci.containerid AND am.workflowstepid <> cci.workflowstepid")
        strSQL.AppendFormat(" WHERE cci.isinstore=0 and cci.workcenterid='{0}'", QueryCondition("WorkerCenterID")) 'update YangJiang 20180604 cci.isinstore=0
        If QueryCondition.Keys.Contains("TeamID") Then
            strSQL.AppendFormat(" AND cci.teamid='{0}'", QueryCondition("TeamID"))
        End If
        strSQL.AppendLine("GROUP BY cci.productname,cci.containerno,am.toolstatus,cci.workflowstepname")
        GetContainerPreparationData1 = DatabaseHelper.Query_ReturnDatTable(strSQL.ToString)
    End Function

    ''' <summary>
    ''' 获取分厂代码  add YangJiang 20180510
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetFactoyCode(ByVal FactoryID As String) As String Implements IBusiness.uLMSDispatchIBusiness.GetFactoyCode
        Dim strSQL = "SELECT ua.attributevalue FROM factory f LEFT JOIN Userattribute ua ON ua.parentid=f.factoryid  WHERE ua.userattributename='分厂代码' AND f.factoryid='" & FactoryID & "'"
        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSQL)
        If dt.Rows.Count = 0 Then
            GetFactoyCode = ""
        Else
            GetFactoyCode = dt.Rows(0)("attributevalue")
        End If
    End Function

    ''' <summary>
    ''' 获取装试阶段报工订单信息 create tianFW 2018年5月4日
    ''' Modify by Yuxj20180612修改机型的获取方式
    ''' 增加ep.workflowstepid is not null 排除施工单 
    ''' Modify by Yuxj20190419
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetStageWorkReportOrderInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSDispatchIBusiness.GetStageWorkReportOrderInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT m.mfgordername,m.mfgorderid,wf.workflowid,ot.ordertypename ordertype,u.attributevalue machinename,")
        strsql.AppendLine("pf.productfamilyname, m.plannedstartdate, m.plannedcompletiondate, m.factorymonth,m.factorystardate, m.factorycompletiondate,p.productid,NVL(a.countnum,0) countnum")
        strsql.AppendLine("，pb.productname,p.description,p.materialcode") 'add YangJiang 20180510
        strsql.AppendLine("FROM mfgorder m")
        strsql.AppendLine("LEFT JOIN Userattribute u ON u.parentid = m.mfgorderid AND u.userattributename='发动机' ")
        'Modify by Yuxj20190327
        'strsql.AppendLine("LEFT JOIN productbase pb1 ON m.productbaseid=pb1.productbaseid ")
        'strsql.AppendLine("LEFT JOIN product p ON p.productid = NVL(pb1.revofrcdid,m.productid) ")
        strsql.AppendLine("LEFT JOIN product p ON p.productid = GET_SPLIT_MFGORDER_PRODUCTID(m.mfgorderid)")

        strsql.AppendLine("LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid ")
        strsql.AppendLine("LEFT JOIN Workflowproductfamilylist wff ON wff.workflowid=p.workflowid ")
        strsql.AppendLine("LEFT JOIN productfamily pf ON wff.productfamilylistid=pf.productfamilyid ")
        strsql.AppendLine(" LEFT JOIN Ordertype ot on ot.ordertypeid = m.ordertypeid")
        strsql.AppendLine("LEFT JOIN Workflowbase wb1 ON wb1.workflowbaseid = p.workflowbaseid")
        strsql.AppendLine("LEFT JOIN Workflow wf ON wf.workflowid = NVL(wb1.revofrcdid,p.workflowid)")
        strsql.AppendLine("LEFT JOIN workflowbase wb ON wb.workflowbaseid = wf.workflowbaseid")
        strsql.AppendLine("LEFT JOIN CONTAINERINSTORE ct ON ct.aufnr=m.mfgordername") 'add YangJiang 20180511
        strsql.AppendLine("INNER JOIN (SELECT count(*) countnum,ep.mfgorderid FROM enginepartplan ep ")
        strsql.AppendFormat("WHERE ep.usecompleted={0} and ep.workflowstepid is not null  GROUP BY ep.mfgorderid", para("usecompleted"))
        strsql.AppendLine(") a ON a.mfgorderid = m.mfgorderid")
        Dim valuestr As New StringBuilder
        If para("usecompleted") = "1" Then
            strsql.AppendLine("LEFT JOIN (")
            strsql.AppendLine("SELECT count(*) countnum_,ep.mfgorderid FROM enginepartplan ep where ep.workflowstepid is not null ")
            strsql.AppendLine("GROUP BY ep.mfgorderid")
            strsql.AppendLine(") b ON b.mfgorderid = a.mfgorderid")
            valuestr.AppendLine("a.countnum = b.countnum_")
        End If
        If para.ContainsKey("engineName") Then
            If valuestr.Length > 0 Then
                valuestr.AppendLine("AND")
            End If
            valuestr.AppendFormat(" u.attributevalue LIKE '%{0}%' ", para("engineName"))
        End If

        If para.ContainsKey("factoryMonth") Then
            If valuestr.Length > 0 Then
                valuestr.AppendLine("AND")
            End If
            valuestr.AppendFormat(" m.factorymonth LIKE '%{0}%'", para("factoryMonth"))
        End If
        'add YangJiang 添加订单号筛选条件 20180510
        If para.ContainsKey("MfgOrderName") Then
            If valuestr.Length > 0 Then
                valuestr.AppendLine("AND")
            End If
            valuestr.AppendFormat(" m.mfgordername LIKE '%{0}%'", para("MfgOrderName"))
        End If
        strsql.AppendLine("WHERE ct.aufnr IS  NULL AND ot.OrderTypeName <> 'PD14' ") 'update YangJiang 20180511 ct.aufnr IS  NULL '订单类型不等于临时订单 add by tianFW 2018年9月4日
        If valuestr.Length > 0 Then
            strsql.AppendFormat(" AND {0} ", valuestr.ToString())
        End If
        strsql.AppendLine(" order by  M.MFGORDERNAME,PB.PRODUCTNAME asc")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strsql.ToString()
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function


    ''' <summary>
    ''' 装试 根据工艺和订单获取工艺工序的状态 create tianFW 2018年5月4日
    ''' </summary>
    ''' <param name="type">查询类型 “engine”-发动机大阶段 其他 </param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetStageWorkflowStepInfo(ByVal type As String, ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetStageWorkflowStepInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT ep.enginepartplanid,ws.workflowstepname,ws.workflowstepid,ep.plannedstartdate,ep.plannedfinishdate,ep.cycletime,")
        strsql.AppendLine("decode(ep.usecompleted,0,'未完成',1,'已完成',NULL) usecompleted")
        If type <> "engine" Then
            strsql.AppendLine(",pb.productname,p.description productdesc, wfb.workflowname||':'||wf.workflowrevision workflowname,ep.containerno")
            'Modify by Yuxj20180928添加完工工序
            strsql.AppendLine(",ws1.workflowstepname CompleteStepName,ep.completestepid ")
        Else
            strsql.AppendLine(",ep.productid,ep.workflowid,cws.workflowstepname CompleteStage,ws.workflowid bigworkflowid ")
        End If
        strsql.AppendLine("FROM enginepartplan ep")
        strsql.AppendLine("LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid")
        If type <> "engine" Then '如果不是大阶段，则查询件号名称、描述、工艺名称版本
            'Modify by Yuxj20180928添加完工工序
            strsql.AppendLine("LEFT JOIN workflowstep ws1 ON ws1.workflowstepid=ep.completestepid ")

            strsql.AppendLine("LEFT JOIN product p ON p.productid = ep.productid")
            strsql.AppendLine("LEFT JOIN productbase pb on pb.productbaseid = p.productbaseid")
            strsql.AppendLine("LEFT JOIN workflow wf ON wf.workflowid = ep.workflowid")
            strsql.AppendLine("LEFT JOIN workflowbase wfb on wfb.workflowbaseid = wf.workflowbaseid")
        Else
            strsql.AppendLine(" left join workflowstep cws on cws.workflowstepid=ep.completestepid")
        End If
        strsql.AppendFormat("  WHERE ep.workflowstepid>'0'   and ep.mfgorderid='{0}'", para("mfgorderid"))
        If type = "engine" Then
            strsql.AppendFormat(" AND ep.workflowid=ws.workflowid AND ep.productid = '{0}'", para("productid"))
            strsql.AppendLine("ORDER BY ws.sequence")
        Else
            strsql.AppendFormat(" AND ep.productid <> '{0}' AND ep.workflowstepid IN ({1})", para("productid"), para("workflowstepid").Replace(",", "','"))
            If para.ContainsKey("Isusecompleted") Then '已完成阶段
                strsql.AppendLine("AND usecompleted=1")
            ElseIf para.ContainsKey("IsNotusecompleted") Then '未完成阶段
                strsql.AppendLine("AND usecompleted=0")
            End If
            strsql.AppendLine("ORDER BY ws.sequence,ep.containerid")
        End If
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' Add by Yuxj20181114
    ''' 撤销大阶段报工中的完工工序
    ''' Modify by Yuxj20190528
    ''' 增加更新WRITETOERPRECORD
    ''' </summary>
    ''' <param name="idList"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function CancelCompleteStep(ByVal idList As String) As Boolean Implements uLMSDispatchIBusiness.CancelCompleteStep
        CancelCompleteStep = False
        Dim strsql As String =
           "  UPDATE enginepartplan ep SET ep.usecompleted='0',ep.finishdate=NULL "
        strsql += String.Format(" WHERE ep.usecompleted='1' AND ep.enginepartplanid IN ('{0}')", idList.Replace(",", "','"))
        DatabaseHelper.ExecuteSql(strsql)

        strsql =
           "  UPDATE WRITETOERPRECORD wt SET wt.isuse='1'"
        strsql += String.Format(" WHERE wt.isuse='0' AND wt.recordid IN ('{0}')", idList.Replace(",", "','"))
        DatabaseHelper.ExecuteSql(strsql)
        CancelCompleteStep = True
    End Function

    ''' <summary>
    ''' 验证装试的批次是否存在报工或者检验 create tianFW 2018年5月17日
    ''' </summary>
    ''' <param name="value"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function VerifyZSContainerExistWorkReportOrCheckInfo(ByVal value As String) As Boolean Implements uLMSDispatchIBusiness.VerifyZSContainerExistWorkReportOrCheckInfo
        VerifyZSContainerExistWorkReportOrCheckInfo = True
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT k.containerstepworkreportid FROM containerstepworkreport k")
        sb.AppendFormat("WHERE {0} AND k.isuse = 0 ", value)
        '验证是否存在报工
        If DatabaseHelper.Query_ReturnDatTable(sb.ToString()).Rows.Count > 0 Then
            Exit Function
        End If
        sb.Clear()
        sb.AppendLine("SELECT k.containercheckinfoid FROM containercheckinfo k")
        sb.AppendFormat("WHERE {0}", value)
        '验证是否存在报工
        If DatabaseHelper.Query_ReturnDatTable(sb.ToString()).Rows.Count > 0 Then
            Exit Function
        End If
        VerifyZSContainerExistWorkReportOrCheckInfo = False
    End Function

    ''' <summary>
    ''' 修改装试的工区长或班长派工 create by tianFW 2018年5月17日
    ''' </summary>
    ''' <param name="containerTeamDispatchInfoID">班组派工唯一id</param>
    ''' <param name="value">修改的值</param>
    ''' <param name="type">修改的类型 Shift-0 Team-1</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function ChangeZSTeamDispatchInfo(ByVal containerTeamDispatchInfoID As String, ByVal value As String, ByVal type As Integer) As Boolean Implements uLMSDispatchIBusiness.ChangeZSTeamDispatchInfo
        ChangeZSTeamDispatchInfo = False
        Dim sb As New StringBuilder
        If type = "0" Then '修改工区长派工信息
            sb.AppendFormat("UPDATE containerteamdispatch ctd SET ctd.shiftid='{0}',ctd.teamid=NULL", value)
        ElseIf type = "1" Then '修改班长派工信息
            sb.AppendFormat("UPDATE containerteamdispatch ctd SET ctd.teamid='{0}'", value)
        End If
        sb.AppendFormat(" WHERE ctd.containerteamdispatchid in ({0})", containerTeamDispatchInfoID.Replace(",", "','"))
        If DatabaseHelper.ExecuteSql(sb.ToString()) > 0 Then
            Return True
        End If
    End Function

    ''' <summary>
    ''' 修改装试的工序工步派工记录信息 createt tianFW 2018年5月18日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateZSDispatchInfo(ByVal para As Dictionary(Of String, Object)) As Boolean Implements uLMSDispatchIBusiness.UpdateZSDispatchInfo
        UpdateZSDispatchInfo = False
        Dim strsql As New StringBuilder
        strsql.AppendLine("UPDATE containerstepdispatch csd SET ")
        strsql.AppendFormat(" csd.plannedstartdate=to_date('{0}','yyyy-MM-dd'),csd.plannedfinishdate=to_date('{1}','yyyy-MM-dd')", para("PlanStartDate"), para("PlanEndDate"))
        strsql.AppendFormat(" WHERE csd.containerstepdispatchid in ({0})", para("ContainerStepDispatchID").ToString().Replace(",", "','"))
        If DatabaseHelper.ExecuteSql(strsql.ToString()) > 0 Then
            strsql.Clear()
            strsql.AppendFormat(" DELETE FROM containerstepdispatchdetail csdd WHERE csdd.containerstepdispatchid IN ({0})", para("ContainerStepDispatchID").ToString().Replace(",", "','"))
            DatabaseHelper.ExecuteSql(strsql.ToString())
            Return True
            ' Remark by Yuxj20190627
            'If DatabaseHelper.ExecuteSql(strsql.ToString()) > 0 Then
            '    Return True
            'End If
        End If
    End Function

    ''' <summary>
    ''' 获取生产派工单件单卡批次信息 create by tianFW 2018年6月12日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetStepDispatchSingleContainer(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetStepDispatchSingleContainer
        Dim strsql As New StringBuilder
        strsql.AppendLine(para("Sql") & " AND ( ")
        If para("ContainerType") = "7" Then
            strsql.AppendFormat(" C.TOGETHERFLAG='{0}'", para("TogatherFlag"))
        Else
            strsql.AppendFormat(" C.CONTAINERNAME='{0}'", para("ContainerName"))
        End If
        strsql.AppendFormat(" AND C.PRODUCTID='{0}' AND C.WORKFLOWSTEPID='{1}'", para("ProductID"), para("WorkflowStepID"))
        strsql.AppendFormat(" AND C.MFGORDERNAME='{0}')", para("MfgOrderName"))
        strsql.AppendLine(" ORDER BY C.CONTAINERNAME")
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 判断工序是否已经进行工具准备申请 add YangJiang 20180705
    ''' </summary>
    ''' <param name="ContainerID"></param>
    ''' <param name="WorkFlowStepID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetIsToolPrepare(ByVal ContainerID As String, ByVal WorkFlowStepID As String) As Boolean Implements IBusiness.uLMSDispatchIBusiness.GetIsToolPrepare
        Dim strSQL As String = "SELECT * FROM arrangements arr WHERE arr.containerid='" & ContainerID & "' AND arr.workflowstepid='" & WorkFlowStepID & "'"
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)
        If dt.Rows.Count = 0 Then
            Return False
        Else
            Return True
        End If
    End Function

    ''' <summary>
    ''' 判断工序是否已经进行了程序准备 add YangJiang 20180709
    ''' </summary>
    ''' <param name="ContainerIDs"></param>
    ''' <param name="WorkFlowStepID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetIsOrNoOrderPrepare(ByVal ContainerIDs As String, ByVal WorkFlowStepID As String) As Boolean Implements IBusiness.uLMSDispatchIBusiness.GetIsOrNoOrderPrepare
        Dim strSQL As String = "SELECT * FROM ncarrangements nc WHERE nc.containerid in(" & ContainerIDs & ") AND nc.workflowstepid='" & WorkFlowStepID & "'"
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)
        If dt.Rows.Count = 0 Then
            Return False
        Else
            Return True
        End If
    End Function

    ''' <summary>
    ''' Add by Yuxj20180719
    ''' 根据工艺Id获取所有工步/工序
    ''' </summary>
    ''' <param name="wfid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetStepInfobyWorkflowId(ByVal wfid As String) As DataTable Implements uLMSDispatchIBusiness.GetStepInfobyWorkflowId
        Dim sb As New StringBuilder
        With sb
            .AppendLine("SELECT st.spectypename,wfs.*,si.*,wf.* FROM workflowstep wfs ")
            .AppendLine("LEFT JOIN workflow wf ON wfs.workflowid=wf.workflowid ")
            .AppendLine("LEFT JOIN stepinfo SI ON wfs.workflowstepid=si.Workflowstepid ")
            .AppendLine(" left join specbase sb on sb.specbaseid=wfs.specbaseid")
            .AppendLine(" LEFT JOIN SPEC S ON S.SPECID = NVL(SB.REVOFRCDID, wfs.SPECID)")
            .AppendLine(" LEFT JOIN spectype st ON st.spectypeid=s.spectypeid")
            .AppendFormat("WHERE wf.workflowid='{0}'", wfid)
        End With
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    ''' <summary>
    ''' Add by Yuxj20180719根据批次ID获取所有派工记录
    ''' </summary>
    ''' <param name="Cid"></param>
    ''' <returns></returns>
    ''' <remarks>add and csd.isuse = 0 by tianFW 2018年9月29日</remarks>
    Function GetDispatchDataByCid(ByVal Cid As String) As DataTable Implements uLMSDispatchIBusiness.GetDispatchDataByCid
        Dim sb As New StringBuilder
        With sb
            .AppendLine("SELECT * FROM CONTAINERSTEPDISPATCH csd ")
            .AppendFormat("WHERE csd.containerid='{0}' and csd.isuse = 0 ", Cid)
        End With
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    ''' <summary>
    ''' Add by Yuxj20181217根据批次ID获取所有检验合格记录
    ''' </summary>
    ''' <param name="Cid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCheckInfoDataByCid(ByVal Cid As String) As DataTable Implements uLMSDispatchIBusiness.GetCheckInfoDataByCid
        Dim sb As New StringBuilder
        With sb
            .AppendLine("SELECT * FROM containercheckinfo csd ")
            .AppendFormat("WHERE csd.containerid='{0}' and csd.isuse = 0 ", Cid)
        End With
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    ''' <summary>
    ''' 根据批次ID或协作工区ID获取存在工区协作但没有完成的信息 create by tianFW 2018年7月25日
    ''' </summary>
    ''' <param name="containerid"></param>
    ''' <param name="toworkcenterid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetNotUseCompletedWorkCenterInfo(ByVal containerid As String, ByVal toworkcenterid As String) As DataTable Implements uLMSDispatchIBusiness.GetNotUseCompletedWorkCenterInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT cwd.*")
        strsql.AppendLine("FROM containerworkcenterdispatch cwd")
        strsql.AppendLine("LEFT JOIN containersteprecord csr ON csr.containerid = cwd.containerid AND csr.workflowstepid = cwd.workflowstepid")
        strsql.AppendLine("WHERE(nvl(csr.usecompleted, 0) = 0)")
        strsql.AppendFormat("AND cwd.containerid ='{0}'  AND cwd.toworkcenterid='{1}' and cwd.isuse = 0 ", containerid, toworkcenterid)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 获取装试派工继承员工信息 create by tianFW 2018年8月1日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetZSLastDispatchInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetZSLastDispatchInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT csdd.workerid,em.fullname workername,tm.teamid,t.teamname FROM(")
        strsql.AppendLine("SELECT * FROM(")
        strsql.AppendLine("SELECT k.*,ROWNUM rn FROM(")
        strsql.AppendLine("SELECT csd.containerstepdispatchid FROM containerstepdispatch csd")
        strsql.AppendLine("LEFT JOIN containercurrentinfo cu ON cu.containerid = csd.containerid")
        strsql.AppendLine("LEFT JOIN mfgorder mfg ON mfg.mfgorderid = cu.mfgorderid")

        'strsql.AppendLine("LEFT JOIN productbase pb_ ON pb_.productbaseid = mfg.productbaseid")
        'strsql.AppendLine("LEFT JOIN product p ON p.productid = nvl(pb_.revofrcdid,mfg.productid)")
        strsql.AppendLine("LEFT JOIN product p ON p.productid = GET_SPLIT_MFGORDER_PRODUCTID(mfg.mfgorderid)")

        strsql.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid")
        strsql.AppendLine("LEFT JOIN Workflowstep ws ON ws.workflowstepid = csd.workflowstepid")
        strsql.AppendLine("LEFT JOIN Workflow wf ON wf.workflowid = ws.workflowid")
        strsql.AppendLine("LEFT JOIN Workflowbase wb ON wb.workflowbaseid = wf.workflowbaseid")
        If para.Keys.Contains("stepinfodesc") And para.Keys.Contains("stepinfoname") Then
            strsql.AppendLine("LEFT JOIN stepinfo SI ON si.Stepinfoid = csd.stepinfoid")
            strsql.AppendLine("LEFT JOIN stepinfobase sio ON sio.stepinfobaseid = si.Stepinfobaseid")
        End If
        strsql.AppendFormat("WHERE csd.isuse = 0 and pb.productname='{0}' AND wb.workflowname='{1}' ", para("machinepname"), para("workflowname"))
        strsql.AppendFormat(" AND cu.productname='{0}' AND ws.workflowstepname='{1}' ", para("productname"), para("workflowstepname"))
        If para.Keys.Contains("stepinfodesc") And para.Keys.Contains("stepinfoname") Then
            strsql.AppendFormat(" AND si.Description='{0}' AND sio.stepinfoname='{1}' ", para("stepinfodesc"), para("stepinfoname"))
        End If
        strsql.AppendLine("ORDER BY csd.createdate DESC")
        strsql.AppendLine(") k")
        strsql.AppendLine(") cd WHERE cd.rn = 1")
        strsql.AppendLine(") kcsd ")
        'Modify by Yuxj20190627 LEFT JOIN-->INNER JOIN
        strsql.AppendLine("INNER JOIN containerstepdispatchdetail csdd ON kcsd.containerstepdispatchid = csdd.containerstepdispatchid")
        strsql.AppendLine("LEFT JOIN teamemployee tm ON tm.employeeid = csdd.workerid")
        strsql.AppendLine("LEFT JOIN employee em ON em.employeeid = csdd.workerid")
        strsql.AppendLine("LEFT JOIN team t ON t.teamid = tm.teamid")
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' Add by Yuxj20180929
    ''' 工区长，班长派工时相同发动机件号的同一部件派工的班组继承功能
    ''' 注意：有缺陷 containerteamdispatch 只有CreateDate记录时间，没有同时记录工区长和班派工的时间，留待后观
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetNearestDispatch(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetNearestDispatch
        Dim sb As New StringBuilder
        'Modify by Yuxj20190221增加工区表，工区与班，班与组ID的判断
        Dim strsql As String =
            <string>
                SELECT ctd.workcenterid,ctd.shiftid,ctd.teamid,s.shiftname,t.teamname FROM containerteamdispatch ctd
                INNER JOIN enginepartplan e ON ctd.containerid=e.containerid 
                INNER JOIN mfgorder mfg ON mfg.mfgorderid=e.mfgorderid 
                --LEFT JOIN productbase pb_ ON pb_.productbaseid = mfg.productbaseid
                --LEFT JOIN product p ON p.productid = nvl(pb_.revofrcdid,mfg.productid)
                LEFT JOIN product p ON p.productid = GET_SPLIT_MFGORDER_PRODUCTID(mfg.mfgorderid)

                LEFT JOIN Workcenter wc ON ctd.workcenterid=wc.workcenterid
                LEFT JOIN shift s ON ctd.shiftid=s.shiftid  AND wc.workcenterid=s.workcenterid
                LEFT JOIN team t ON t.teamid=ctd.teamid  AND t.shiftid=s.shiftid
            </string>
        sb.AppendLine(strsql)
        sb.AppendFormat(" where p.productid='{0}' AND e.productid='{1}' ", para("MfgProductId"), para("ProductId"))
        sb.AppendFormat(" and ctd.workcenterid='{0}' ", para("WorkCenterId"))
        sb.AppendFormat("and ctd.factoryid='{0}'", para("FactoryId"))

        'Modify by Yuxj20190516
        If para.ContainsKey("wfid") Then
            sb.AppendFormat("and e.workflowid='{0}'", para("wfid"))
        End If
        If para("Flag") = "T" Then
            sb.AppendFormat(" and ctd.shiftid='{0}'", para("ShiftId"))
            sb.AppendFormat(" and ctd.teamid is not null ")
            sb.AppendLine(" order by nvl(CTD.TOTEAMDISPATCHDATE,ctd.createdate) DESC ")
        Else
            sb.AppendLine(" order by ctd.createdate desc")
        End If

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        'Modify by Yuxj20190221
        If para("Flag") = "T" Then
            Dim dv As DataView = dt.DefaultView
            dv.RowFilter = "teamname is not null"
            dt = dv.ToTable()
        End If
        Return dt
    End Function

    ''' <summary>
    ''' 获取工序生产排产时间　create by tianFW 2018年10月15日 
    ''' </summary>
    ''' <param name="containerID">批次ID</param>
    ''' <param name="workflowstepID">工序ID</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkflowstepPlanTimeInfo(ByVal containerID As String, ByVal workflowstepID As String) As DataTable Implements uLMSDispatchIBusiness.GetWorkflowstepPlanTimeInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT * from containerstepplan csp")
        strsql.AppendFormat(" WHERE csp.containerid in ({0}) AND csp.workflowstepid='{1}'", containerID, workflowstepID)
        strsql.AppendLine("ORDER BY csp.plannedfinishdate DESC")
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' Add by Yuxj20181128
    ''' 获取工区，班派工信息为撤销使用
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerRevTeamDispatch(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetContainerRevTeamDispatch
        Dim sb As New StringBuilder
        Dim strsql As String =
            <string>
                  SELECT EP.CONTAINERNO       TOGETHERFLAG,
                         CT.CONTAINERID,
                         EP.CONTAINERNAME,
                         WFB.WORKFLOWNAME,
                         PB.PRODUCTNAME,
                         EP.PRODUCTID,
                         P.DESCRIPTION        PRODUCTDESCRIPTION,
                         WF.DESCRIPTION       WFDESC,
                         WFS.WORKFLOWSTEPID,
                         WFS.WORKFLOWSTEPNAME,
                         S.SHIFTID,
                         S.SHIFTNAME,
                         T.TEAMID,
                         T.TEAMNAME,
                         WC.WORKCENTERNAME,
                         CT.CONTAINERTEAMDISPATCHID,ep.type
                    FROM ENGINEPARTPLAN EP
                   INNER JOIN CONTAINERTEAMDISPATCH CT
                      ON CT.CONTAINERID = NVL(EP.CONTAINERID, EP.ENGINECONTAINERID)
                    LEFT JOIN WORKFLOW WF
                      ON EP.WORKFLOWID = WF.WORKFLOWID
                    LEFT JOIN WORKFLOWBASE WFB
                      ON WF.WORKFLOWBASEID = WFB.WORKFLOWBASEID
                    LEFT JOIN WORKFLOWSTEP WFS
                      ON EP.WORKFLOWSTEPID = WFS.WORKFLOWSTEPID
                    LEFT JOIN PRODUCT P
                      ON P.PRODUCTID = EP.PRODUCTID
                    LEFT JOIN PRODUCTBASE PB
                      ON P.PRODUCTBASEID = PB.PRODUCTBASEID
                    LEFT JOIN SHIFT S
                      ON CT.SHIFTID = S.SHIFTID
                    LEFT JOIN TEAM T
                      ON CT.TEAMID = T.TEAMID
                    LEFT JOIN WORKCENTER WC
                      ON CT.WORKCENTERID = WC.WORKCENTERID
            </string>
        sb.AppendLine(strsql)
        sb.AppendFormat("where ct.factoryid='{0}' AND ep.usecompleted=0 and ct.isuse='0' ", para("FactoryID"))
        If para.ContainsKey("ContainerNo") Then
            sb.AppendFormat(" and ep.containerno like '%{0}%'", para("ContainerNo"))
        End If
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    ''' <summary>
    ''' Add by Yuxj20181128
    ''' 获取派报检的数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerRevStepDispatch(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetContainerRevStepDispatch
        Dim sb As New StringBuilder
        Dim strsql As String =
            <string>                
                SELECT EP.CONTAINERNO,
                       EP.CONTAINERID,
                       CD.CONTAINERSTEPDISPATCHID,
                       WFS.WORKFLOWSTEPID,
                       WFS.WORKFLOWSTEPNAME,
                       SI.STEPINFOID,
                       SIB.STEPINFONAME,
                       SI.DESCRIPTION STEPINFODESC,
                       CSK.CONTAINERSTEPWORKREPORTID,
                       GETEMPFULLNAME(CD.recvemployeeid) DISPATCHER,
                       GETEMPFULLNAME(CSK.WORKERID) WORKER,
                       GETEMPFULLNAME(CCK.CHECKERID) CHECKER,
                       CD.CREATEDATE DISPATCHDATE,
                       CSK.CREATEDATE WORKDATE,
                       CCK.CREATEDATE CHECKDATE,
                       DECODE(csk.createdate,NULL,'未报工','已报工') iswork,
                       DECODE(CCK.createdate,NULL,'未检验','已检验') ischeck,
                       CD.CONTAINERSTEPDISPATCHID,
                       CSK.CONTAINERSTEPWORKREPORTID,
                       CCK.CONTAINERCHECKINFOID   
                  FROM CONTAINERSTEPDISPATCH CD
                  LEFT JOIN CONTAINERSTEPWORKREPORT CSK
                    ON CD.CONTAINERSTEPDISPATCHID = CSK.CONTAINERSTEPDISPATCHID AND CSK.ISUSE = '0'
                  LEFT JOIN CONTAINERCHECKINFO CCK
                    ON CSK.CONTAINERSTEPWORKREPORTID = CCK.CONTAINERSTEPWORKREPORTID AND CCK.ISUSE = '0'
                  LEFT JOIN ENGINEPARTPLAN EP
                    ON CD.CONTAINERID = nvl(EP.CONTAINERID,ep.enginecontainerid)
                  LEFT JOIN WORKFLOWSTEP WFS
                    ON CD.WORKFLOWSTEPID = WFS.WORKFLOWSTEPID
                  LEFT JOIN STEPINFO SI
                    ON CD.STEPINFOID = SI.STEPINFOID
                  LEFT JOIN STEPINFOBASE SIB
                    ON SI.STEPINFOID = SIB.REVOFRCDID
                 WHERE EP.USECOMPLETED = '0'
                   AND CD.ISUSE = '0'
            </string>
        sb.AppendLine(strsql)
        If para.ContainsKey("ContainerNo") Then
            sb.AppendFormat(" and ep.containerno like '%{0}%'", para("ContainerNo"))
        End If

        If para.ContainsKey("ContainerId") Then
            sb.AppendFormat(" and cd.ContainerId ='{0}' ", para("ContainerId"))
        End If
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' Add by Yuxj20181129
    ''' 判断批次是否有生产派工
    ''' </summary>
    ''' <param name="cid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function isStepDispatch(ByVal cid As String) As Boolean Implements uLMSDispatchIBusiness.isStepDispatch
        isStepDispatch = False
        Dim strsql As String = String.Format(" select * from containerstepdispatch where containerid='{0}'  and isuse='0' ", cid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        If dt.Rows.Count > 0 Then
            Return True
        End If
    End Function

    ''' <summary>
    ''' Add by Yuxj20181129
    ''' 撤销装试组派工
    ''' </summary>
    ''' <param name="id"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function ReverseTeamDispatchZS(ByVal id As String) As Boolean Implements uLMSDispatchIBusiness.ReverseTeamDispatchZS
        ReverseTeamDispatchZS = False
        Dim strsql As String = String.Format(" update containerstepdispatch set teamid=null,toteamdispatchdate=null,toteamdispatcherid=null ")
        strsql += String.Format(" where containerstepdispatchid='{0}' and isuse='0'", id)
        DatabaseHelper.ExecuteSql(strsql)
        Return True
    End Function

    ''' <summary>
    ''' Add by Yuxj20181205 任务单批量生产派工
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="ary"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DispatchAllSelectByWorkflow(ByVal para As Dictionary(Of String, String), ByVal ary As Array, ByVal paraInfo As Dictionary(Of String, Object), Optional ByVal isZs As Boolean = False) As Boolean Implements uLMSDispatchIBusiness.DispatchAllSelectByWorkflow
        DispatchAllSelectByWorkflow = False
        Dim Wfid As String = String.Empty, Cid As String = String.Empty
        Dim dtC As New DataTable
        Dim dtWf As New DataTable
        Dim drs As DataRow()

        Dim m_UserInfo As CurrentUserInfo = paraInfo("UserInfo")
        For k As Integer = 0 To para.Count - 1
            Wfid = para.Values(k).ToString.Split(",")(0)
            Cid = para.Keys(k).ToString
            Dim isroutinetest As String = para.Values(k).ToString.Split(",")(1)
            dtWf = GetStepInfobyWorkflowId(Wfid)

            If String.IsNullOrWhiteSpace(isroutinetest) Or isroutinetest = "0" Then '不是例行试验单号
                Dim routinetestDrs As DataRow() = dtWf.Select("SpecTypeName='例行试验'")
                If routinetestDrs.Length > 0 Then
                    Dim routinetestDt As DataTable = routinetestDrs.CopyToDataTable
                    '求差
                    Dim obj = dtWf.AsEnumerable.Except(routinetestDt.AsEnumerable, DataRowComparer.Default)
                    If obj.Count > 0 Then
                        dtWf = obj.CopyToDataTable
                    End If
                    dtWf.AcceptChanges()
                End If
            End If

            '判断是不是施工工艺，施工工艺只派本组Yuxj20181218
            If CheckWorkFlowType(Wfid) Then
                Dim dv = dtWf.DefaultView
                dv.RowFilter = String.Format("teamid='{0}'", m_UserInfo.TeamID)
                dtWf = dv.ToTable
            End If
            dtC = GetDispatchDataByCid(Cid)
            Dim beginDate As Date = Date.Parse(paraInfo(Cid).ToString.Split(",")(0))
            Dim endDate As Date = Date.Parse(paraInfo(Cid).ToString.Split(",")(1))
            '删除已经派工的记录
            For j As Integer = dtWf.Rows.Count - 1 To 0 Step -1
                Dim DTO As ContainerStepDispatchDTO = New ContainerStepDispatchDTO
                DTO.ContainerID = Cid
                DTO.FactoryID = m_UserInfo.FactoryID
                DTO.WorkCenterID = m_UserInfo.WorkCenterID
                DTO.TeamID = m_UserInfo.TeamID
                DTO.ResourceID = ""
                DTO.WorkerID = "" '全部操作员存储在派工子表中
                DTO.Qty = 1
                DTO.PlannedStartDate = beginDate 'Date.Parse(paraInfo("BeginDate"))
                DTO.PlannedFinishDate = endDate 'Date.Parse(paraInfo("EndDate"))
                DTO.ContainerStepDispatchID = System.Guid.NewGuid.ToString()
                DTO.EmployeeID = m_UserInfo.EmployeeID
                DTO.CreateDate = System.DateTime.Now()
                DTO.IsUse = 0
                DTO.PriorityCode = 1
                DTO.WorkflowstepID = dtWf.Rows(j)("workflowstepid").ToString
                If IsDBNull(dtWf.Rows(j)("stepinfoid")) Then
                    DTO.stepID = String.Empty
                    drs = dtC.Select(String.Format("workflowstepid='{0}'", dtWf.Rows(j)("workflowstepid").ToString))

                Else
                    DTO.stepID = dtWf.Rows(j)("StepInfoID").ToString
                    drs = dtC.Select(String.Format("StepInfoID='{0}'", dtWf.Rows(j)("StepInfoID").ToString))

                End If
                If drs.Length > 0 Then
                    '  dtWf.Rows.RemoveAt(j)
                    Continue For
                End If

                If AddContainerStepDispatchInfo(DTO) Then
                    For i As Integer = 0 To ary.Length - 1
                        If AddDispatchdetails(DTO.ContainerStepDispatchID, ary(i), True) Then
                            DispatchAllSelectByWorkflow = True
                        End If
                    Next
                End If
            Next
        Next

    End Function

    ''' <summary>
    ''' Add by Yuxj20181206
    ''' 判断大阶段是否有任务单（大阶段报工及撤销时用于卡关）
    ''' </summary>
    ''' <param name="PlanId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function HasContainerNo(ByVal PlanId As String) As Boolean Implements uLMSDispatchIBusiness.HasContainerNo
        HasContainerNo = False
        Dim sb As New StringBuilder
        sb.AppendFormat(" SELECT ep.containerno,ep.mfgorderid,ep.workflowstepid   ")
        sb.AppendFormat(" FROM enginepartplan ep WHERE ep.enginepartplanid='{0}' ", PlanId)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            If Not IsDBNull(dt.Rows(0).Item("containerno")) Then
                HasContainerNo = True
            Else
                Dim mfgorderid As String = dt.Rows(0).Item("mfgorderid").ToString
                Dim workflowstepid As String = dt.Rows(0).Item("workflowstepid").ToString
                sb = New StringBuilder
                sb.AppendFormat(" SELECT ep.containerno,ep.mfgorderid,ep.workflowstepid FROM enginepartplan ep   ")
                sb.AppendFormat(" WHERE ep.mfgorderid='{0}' and ep.workflowstepid='{1}' and ep.containerno is not null ", mfgorderid, workflowstepid)
                dt = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
                If dt.Rows.Count > 0 Then
                    HasContainerNo = True
                End If
            End If
        End If
    End Function


    ''' <summary>
    ''' Add by Yuxj20181218
    ''' 获取施工单是否已经对本工序派工
    ''' </summary>
    ''' <param name="Cid"></param>
    ''' <param name="wfsid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function HasStepDispatch(ByVal Cid As String, ByVal wfsid As String) As Boolean Implements uLMSDispatchIBusiness.HasStepDispatch
        HasStepDispatch = True
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT * FROM containerstepdispatch csd  ")
        sb.AppendFormat(" WHERE csd.containerid='{0}' AND csd.workflowstepid='{1}' and csd.Isuse='0' ", Cid, wfsid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count = 0 Then
            HasStepDispatch = False
        End If
    End Function

    ''' <summary>
    ''' Add by Yuxj20181218
    ''' 更新工序组
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateWfsTeam(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSDispatchIBusiness.UpdateWfsTeam
        UpdateWfsTeam = False
        Dim sb As New StringBuilder
        sb.AppendFormat("update workflowstep wfs set wfs.teamid='{0}' where wfs.WorkFlowStepId='{1}'  ", para("TeamId"), para("WorkFlowStepId"))
        DatabaseHelper.ExecuteSql(sb.ToString)
        UpdateWfsTeam = True
    End Function

    ''' <summary>
    ''' Add by Yuxj20181218
    ''' 更新班派工组
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateTeamDispatchTeam(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSDispatchIBusiness.UpdateTeamDispatchTeam
        UpdateTeamDispatchTeam = False
        Dim sb As New StringBuilder
        sb.AppendLine("update containerteamdispatch csd  set ")
        sb.AppendFormat(" csd.Teamid='{0}',csd.shiftid='{1}',csd.workcenterid='{2}' ", para("TeamId"), para("ShiftId"), para("WorkCenterId"))

        sb.AppendFormat(" where  csd.workflowstepid='{0}'", para("WorkFlowStepId"))
        If para.ContainsKey("ContainerId") Then
            sb.AppendFormat(" and csd.containerid='{0}' ", para("ContainerId"))
        End If
        DatabaseHelper.ExecuteSql(sb.ToString)
        UpdateTeamDispatchTeam = True
    End Function

    ''' <summary>
    ''' Add by Yuxj20181218
    ''' 如果为施工单工艺，返回True,否则False
    ''' </summary>
    ''' <param name="workflowid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function CheckWorkFlowType(ByVal workflowid As String) As Boolean Implements uLMSDispatchIBusiness.CheckWorkFlowType
        CheckWorkFlowType = False
        Dim strSql As String = String.Format("SELECT * FROM workflow wf WHERE wf.workflowtype='施工工艺' and wf.workflowid='{0}' ", workflowid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dt.Rows.Count > 0 Then
            CheckWorkFlowType = True
        End If
    End Function

    ''' <summary>
    ''' 更新批次派工状态 
    ''' </summary>
    ''' <param name="containerid">批次ID</param>
    ''' <param name="type">1 存在派工 dispatchnum 2 当前序派工 isdispatch</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateContainerDispatchNum(ByVal containerid As String, ByVal type As String) As Boolean Implements uLMSDispatchIBusiness.UpdateContainerDispatchNum
        Dim objPara As Object()
        objPara = FillUpdateContainerDispatchNum(containerid, type)
        If DatabaseHelper.ExecuteSql(objPara.First) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function FillUpdateContainerDispatchNum(ByVal containerid As String, ByVal type As String) As Object() Implements uLMSDispatchIBusiness.FillUpdateContainerDispatchNum
        Dim strsql As New StringBuilder
        strsql.AppendLine("UPDATE containercurrentinfo cu SET")
        If type = 1 Then
            strsql.AppendFormat(" cu.dispatchnum = 1 WHERE cu.containerid='{0}'", containerid)
        Else
            strsql.AppendFormat(" cu.dispatchnum = 1,cu.isdispatch = 1 WHERE cu.containerid='{0}'", containerid)
        End If
        Return New Object() {strsql.ToString()}

    End Function

    ''' <summary>
    ''' 更新批次派工记录的状态 add by tianFW 2019年1月15日
    ''' </summary>
    ''' <param name="ContainerStepDispatchIDs"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateDispatchIsCompleteStatus(ByVal ContainerStepDispatchIDs As String) As Boolean Implements uLMSDispatchIBusiness.UpdateDispatchIsCompleteStatus
        SyncLock threadLock
            UpdateDispatchIsCompleteStatus = False
            Dim strsql As New StringBuilder
            Dim str_Value As String =
                <string>
               SELECT CSD.CONTAINERSTEPDISPATCHID, NVL(CSD.ISCOMPLETE, 0) ISCOMPLETE
	           FROM CONTAINERSTEPDISPATCH CSD
               LEFT JOIN containercurrentinfo cci ON cci.containerid = csd.containerid
               LEFT JOIN (SELECT NVL(SUM(CSW.QTY), 0) REPORTTOTALQTY,CSW.CONTAINERSTEPDISPATCHID
							    FROM CONTAINERSTEPWORKREPORT CSW
            </string>
            strsql.AppendLine(str_Value)
            strsql.AppendFormat(" WHERE ({0})", ContainerStepDispatchIDs)
            str_Value =
                <string>
                                AND CSW.ISUSE = 0 GROUP BY CSW.CONTAINERSTEPDISPATCHID) DISCSW
		        ON DISCSW.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID
                WHERE ((CSD.QTY > DISCSW.REPORTTOTALQTY AND NVL(CSD.ISCOMPLETE, 0) = 1)
                OR (DISCSW.CONTAINERSTEPDISPATCHID IS NULL AND NVL(CSD.ISCOMPLETE,0) = 1)
            </string>
            strsql.AppendLine(str_Value)
            strsql.AppendLine("OR (cci.qty <= DISCSW.REPORTTOTALQTY)") '已报工总数 >= 批次在制数 add zhunan 2024.11.28
            strsql.AppendLine("OR (CSD.QTY <= DISCSW.REPORTTOTALQTY AND NVL(CSD.ISCOMPLETE, 0) = 0))")
            strsql.AppendFormat(" AND ({0})", ContainerStepDispatchIDs.Replace("csw", "csd"))
            Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
            If dt.Rows.Count > 0 Then
                Dim SQLStringList As New Dictionary(Of String, OracleParameter())
                Dim sqlValue As String = ""
                For Each dr As DataRow In dt.Rows
                    sqlValue = "UPDATE CONTAINERSTEPDISPATCH CSD SET "
                    If Integer.Parse(dr("ISCOMPLETE")) = 1 Then
                        sqlValue += " CSD.ISCOMPLETE = 0 "
                    Else
                        sqlValue += " CSD.ISCOMPLETE = 1 "
                    End If
                    sqlValue += String.Format(" WHERE CSD.CONTAINERSTEPDISPATCHID = '{0}'", dr("CONTAINERSTEPDISPATCHID"))
                    SQLStringList.Add(sqlValue, Nothing)
                Next
                DatabaseHelper.ExecuteSqlTranByHash(SQLStringList)
            End If
            UpdateDispatchIsCompleteStatus = True
        End SyncLock
    End Function

#Region "Add by Yuxj20190329 增加班组改派及工序改派的卡关"
    ''' <summary>
    ''' 判断是否派工到班组
    ''' </summary>
    ''' <param name="cid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function CheckTeamDispatch(ByVal cid As String) As DataTable Implements uLMSDispatchIBusiness.CheckTeamDispatch
        CheckTeamDispatch = New DataTable
        Dim strSql As String = "SELECT cd.teamid,cd.shiftid,ep.containerno FROM containerteamdispatch cd "
        strSql += "LEFT JOIN enginepartplan ep ON cd.containerid=NVL(ep.containerid,ep.enginecontainerid) "
        strSql += String.Format(" WHERE cd.teamid is not null and cd.containerid='{0}' and  cd.isuse=0 ", cid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        CheckTeamDispatch = dt
    End Function

    ''' <summary>
    ''' 判断是否派工到工序
    ''' </summary>
    ''' <param name="cid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function CheckStepDispatch(ByVal cid As String) As DataTable Implements uLMSDispatchIBusiness.CheckStepDispatch
        CheckStepDispatch = New DataTable
        Dim strSql As String = "SELECT ep.containerno FROM containerstepdispatch cd "
        strSql += "LEFT JOIN enginepartplan ep ON cd.containerid=NVL(ep.containerid,ep.enginecontainerid) "
        strSql += String.Format("WHERE cd.containerid='{0}' and cd.isuse=0 ", cid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        CheckStepDispatch = dt
    End Function

    ''' <summary>
    ''' 判断是否工序报工
    ''' </summary>
    ''' <param name="containerstepdispatchid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function CheckStepWorkReport(ByVal containerstepdispatchid As String) As DataTable Implements uLMSDispatchIBusiness.CheckStepWorkReport
        CheckStepWorkReport = New DataTable
        Dim strSql As String = "SELECT ep.containerno FROM containerstepdispatch cd "
        strSql += " LEFT JOIN containerstepworkreport csw ON csw.containerstepdispatchid=cd.containerstepdispatchid "
        strSql += "LEFT JOIN enginepartplan ep ON cd.containerid=NVL(ep.containerid,ep.enginecontainerid) "
        strSql += String.Format("WHERE cd.containerstepdispatchid='{0}' and cd.isuse=0 and csw.isuse=0 ", containerstepdispatchid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        CheckStepWorkReport = dt
    End Function
#End Region
    ''' <summary>
    ''' 更新分解表工区yuxj20190516
    ''' </summary>
    ''' <param name="wcid"></param>
    ''' <param name="enginelistid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function updateEngineWcid(ByVal wcid As String, ByVal enginelistid As String) As Boolean Implements uLMSDispatchIBusiness.updateEngineWcid
        updateEngineWcid = False
        Dim strsql As String =
            String.Format(" update enginepartplan ep set workcenterid='{0}',workcenterdate=sysdate where ep.enginepartplanid in ('{1}')", wcid, enginelistid.Replace(",", "','"))
        DatabaseHelper.ExecuteSql(strsql)
        updateEngineWcid = True
    End Function
    ''' <summary>
    ''' Modify by Yuxj20190522
    ''' 获取班组派工记录
    ''' </summary>
    ''' <param name="cid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetTeamShiftDispatch(ByVal cid As String) As DataTable Implements uLMSDispatchIBusiness.GetTeamShiftDispatch
        Dim strsql As String =
            String.Format(" select * from containerteamdispatch cd where cd.containerid='{0}' ", cid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        Return dt
    End Function

#Region "工步派工 add by zhangrj 20220728"
    ''' <summary>
    ''' 获取待派工批次列表（其他分厂可通用，暂时未启用，启用待优化）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerStepInfoDispatchList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSDispatchIBusiness.GetContainerStepInfoDispatchList
        Dim strSelect = <string>
                        select 
K.PRODUCTNAME,K.PRODUCTID,K.WORKFLOWSTEPNAME,K.WORKFLOWSTEPID,K.SPECNO,K.WORKFLOWID,K.SEQUENCE,K.TOGETHERFLAG,
          K.CONTAINERTYPE,K.MFGORDERNAME,K.PLANQTY,K.PLANNEDSTARTDATE,K.PLANNEDCOMPLETIONDATE,K.WORKCENTERNAME,K.WORKCENTERID,K.TOGETHERFLAGNAMES,
          K.FACTORYID,K.PRODUCTDESCRIPTION,K.PRODUCTREVISION,sum(K.WIPQTY) WIPQTY,K.CONTAINERTYPENAME,K.ProductBatch,k.containerno
 from  (
SELECT DISTINCT C.CONTAINERID, NVL(C.WORKFLOWROUTE,C.PRODUCTNAME) PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE,C.TOGETHERFLAG, 
C.CONTAINERTYPE,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.PLANQTY,C.QTY WIPQTY,C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,
C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,DECODE(C.CONTAINERTYPE,7,C.TOGETHERFLAG,C.CONTAINERNAME) TOGETHERFLAGNAMES,
(nvl(c.Workflowroute,c.productname)||'/'||c.containerno) ProductBatch,c.containerno
                     FROM containercurrentinfo c
                    LEFT JOIN (
                      SELECT s.stepinfoid,SUM(nvl(csd.qty,0)) disqty,c.containerid,ws.workflowstepid
                     FROM containercurrentinfo c
                     LEFT JOIN workflowstep ws ON ws.workflowstepid = c.workflowstepid
                     LEFT JOIN stepinfo s ON s.workflowstepid = ws.workflowstepid
              LEFT JOIN containerstepdispatch2 csd ON csd.containerid = c.containerid AND csd.workflowstepid = c.workflowstepid AND csd.isuse = 0 AND csd.stepinfoid = s.stepinfoid     
                      WHERE  c.originalfactoryid = '{0}' AND c.isinstore = 0  AND c.splitnum = 0 AND c.containerstatus = 1
                     GROUP BY s.stepinfoid,c.containerid,ws.workflowstepid
                     ) cc ON cc.containerid = c.containerid AND cc.workflowstepid = c.workflowstepid
                      WHERE  c.originalfactoryid = '{0}' AND c.isinstore = 0  AND c.splitnum = 0 AND c.containerstatus = 1
                      and cc.disqty {1} c.qty
                        </string>.Value
        strSelect = String.Format(strSelect, para("FactoryID").ToString, "<")

        If para.Keys.Contains("ScanContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ScanContainerName")) Then
                If para("ContainerType") = "7" Then
                    strSelect += " AND c.togetherflag = '" + para("ScanContainerName") + "'"
                Else
                    strSelect += " AND c.containername = '" + para("ScanContainerName") + "'"
                End If
            End If
        End If
        If para.Keys.Contains("mfgOrderName") Then
            If Not String.IsNullOrWhiteSpace(para("mfgOrderName")) Then
                strSelect += " AND c.mfgordername = '" + para("mfgOrderName") + "'"
            End If
        End If
        If para.Keys.Contains("ProductName") Then
            If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
                strSelect += " AND c.productname like '%" + para("ProductName") + "%'"
            End If
        End If
        If para.Keys.Contains("ContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                strSelect += " AND c.containername like '%" + para("ContainerName") + "%'"
            End If
        End If
        If para.Keys.Contains("StepNo") Then
            If Not String.IsNullOrWhiteSpace(para("StepNo")) Then
                strSelect += " AND c.workflowstepname like '%" + para("StepNo") + "%'"
            End If
        End If
        If para.Keys.Contains("WorkCenterID") Then
            If Not String.IsNullOrWhiteSpace(para("WorkCenterID")) Then
                strSelect += String.Format("  AND (c.WORKCENTERID='{0}' OR (c.TOWORKCENTERID='{0}' AND c.WORKCENTERDISPATCHNUM=0) OR (c.FROMWORKCENTERID='{0}' AND c.ISSHIP=1) or c.STEPWORKCENTERIDS  LIKE '%{0}%' ) ", para("WorkCenterID"))
            End If
        End If
        strSelect += <string>
) K
GROUP BY  K.PRODUCTNAME,K.PRODUCTID,K.WORKFLOWSTEPNAME,K.WORKFLOWSTEPID,K.SPECNO,K.WORKFLOWID,K.SEQUENCE,K.TOGETHERFLAG,
          K.CONTAINERTYPE,K.MFGORDERNAME,K.PLANQTY,K.PLANNEDSTARTDATE,K.PLANNEDCOMPLETIONDATE,K.WORKCENTERNAME,K.WORKCENTERID,K.TOGETHERFLAGNAMES,
          K.FACTORYID,K.PRODUCTDESCRIPTION,K.PRODUCTREVISION,K.WIPQTY,K.CONTAINERTYPENAME,K.ProductBatch,k.containerno
           ORDER BY K.PRODUCTNAME,K.TOGETHERFLAG
                   </string>.Value

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSelect
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取待派工批次列表（热表）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetRBContainerStepInfoDispatchList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSDispatchIBusiness.GetRBContainerStepInfoDispatchList
        Dim strSelect1 = <string>                  
                      SELECT distinct c.containerid,C.PRODUCTNAME,C.WORKFLOWSTEPNAME, C.WORKFLOWID,C.CONTAINERTYPE,
                     DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,
                     C.PLANQTY,C.QTY WIPQTY,C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,
                     C.PRODUCTDESCRIPTION,(nvl(c.Workflowroute,c.productname)||'/'||c.containerno) ProductBatch
                     FROM containercurrentinfo c
                     LEFT JOIN containersubcontainer csc ON csc.subcontainerid = c.containerid
                     LEFT JOIN containercurrentinfo fa ON fa.containerid = csc.containerid
                     LEFT JOIN containershiprecord csr ON csr.containerid = fa.containerid AND csr.isuse = 0 AND csr.toacceptdate IS NOT NULL
                     LEFT JOIN containershiprecordstep csrs ON csrs.containershiprecordid = csr.containershiprecordid 
                     LEFT JOIN workflowstep ws ON ws.workflowstepid = csrs.workflowstepid 
                     WHERE c.originalfactoryid = '{0}' AND c.isinstore = 0  AND c.splitnum = 0 AND c.containerstatus = 1
                     AND (ws.workflowstepid = csc.workflowstepid OR ws.workflowstepname = csc.workflowstepname) --转工接收的
                     {1}
                     AND NOT EXISTS( --没有派完工的
                        SELECT 1 FROM containerstepdispatch csd WHERE csd.containerid = c.containerid AND csd.workflowstepid = c.workflowstepid AND csd.isuse = 0   
                      )
                      AND NOT EXISTS( --不是合卡批次
                      SELECT 1 FROM containercombinate cb WHERE cb.containerid = c.containerid
                      )
                      
                      UNION ALL
                      
                     SELECT c.containerid,C.PRODUCTNAME,C.WORKFLOWSTEPNAME, C.WORKFLOWID,C.CONTAINERTYPE,
                     DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,
                     C.PLANQTY,C.QTY WIPQTY,C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,
                     C.PRODUCTDESCRIPTION,(nvl(c.Workflowroute,c.productname)||'/'||c.containerno) ProductBatch
                     FROM containercurrentinfo c
                     LEFT JOIN containersubcontainer csc ON csc.subcontainerid = c.containerid
                     WHERE c.originalfactoryid = '{0}' AND c.isinstore = 0  AND c.splitnum = 0 AND c.containerstatus = 1
                     AND csc.containersubcontainerid IS NULL --不是转工开卡批次
                     {1}
                     AND NOT EXISTS( --没有派完工的
                        SELECT 1 FROM containerstepdispatch csd WHERE csd.containerid = c.containerid AND csd.workflowstepid = c.workflowstepid AND csd.isuse = 0   
                      )
                      AND NOT EXISTS( --不是合卡批次
                      SELECT 1 FROM containercombinate cb WHERE cb.containerid = c.containerid
                      )
                      AND EXISTS( --无订单批次接收的
                          SELECT 1 FROM rbordercontainerreceive r WHERE r.containerid = c.containerid AND r.receivedate IS NOT NULL
                      )
                        </string>.Value
        Dim strSelect2 = <string>                  
                      SELECT '' containerid, C.PRODUCTNAME,C.WORKFLOWSTEPNAME, C.WORKFLOWID,C.CONTAINERTYPE,
                      DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,
                      null PLANQTY,SUM(c.qty) WIPQTY,NULL PLANNEDSTARTDATE, NULL PLANNEDCOMPLETIONDATE,
                      C.PRODUCTDESCRIPTION,cb.containercombinatename ProductBatch
                      FROM containercombinate cb
                      LEFT JOIN containercurrentinfo c ON c.containerid = cb.containerid
                      WHERE  c.originalfactoryid = '{0}' AND c.isinstore = 0 AND c.containerstatus = 1
                      {1}
                      AND NOT EXISTS(
                        SELECT 1 FROM containerstepdispatch csd WHERE csd.containerid = c.containerid AND csd.workflowstepid = c.workflowstepid AND csd.isuse = 0   
                      )
                      GROUP BY C.PRODUCTNAME,C.WORKFLOWSTEPNAME, C.WORKFLOWID, 
                      C.CONTAINERTYPE,C.MFGORDERNAME,C.PRODUCTDESCRIPTION,cb.containercombinatename
                        </string>.Value

        Dim sqlWhere = ""
        Dim strSelect = String.Format(<string>
                                          {0}
                                          UNION ALL
                                          {1}
                                      </string>.Value, strSelect1, strSelect2)
        If para.Keys.Contains("CombinateName") Then
            If Not String.IsNullOrWhiteSpace(para("CombinateName")) Then
                strSelect = strSelect2
                sqlWhere += " AND cb.containercombinatename like '%" + para("CombinateName") + "%'"
            End If
        End If
        '通用查询
        If para.Keys.Contains("ScanContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ScanContainerName")) Then
                sqlWhere += " AND c.containername = '" + para("ScanContainerName") + "'"
            End If
        End If
        If para.Keys.Contains("mfgOrderName") Then
            If Not String.IsNullOrWhiteSpace(para("mfgOrderName")) Then
                sqlWhere += " AND c.mfgordername = '" + para("mfgOrderName") + "'"
            End If
        End If
        If para.Keys.Contains("ProductName") Then
            If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
                sqlWhere += " AND c.productname like '%" + para("ProductName") + "%'"
            End If
        End If
        If para.Keys.Contains("ContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                sqlWhere += " AND c.containername like '%" + para("ContainerName") + "%'"
            End If
        End If
        If para.Keys.Contains("StepNo") Then
            If Not String.IsNullOrWhiteSpace(para("StepNo")) Then
                sqlWhere += " AND c.workflowstepname like '%" + para("StepNo") + "%'"
            End If
        End If
        If para.Keys.Contains("WorkCenterID") Then
            If Not String.IsNullOrWhiteSpace(para("WorkCenterID")) Then
                sqlWhere += String.Format("  AND (c.WORKCENTERID='{0}' OR (c.TOWORKCENTERID='{0}' AND c.WORKCENTERDISPATCHNUM=0) OR (c.FROMWORKCENTERID='{0}' AND c.ISSHIP=1) or c.STEPWORKCENTERIDS  LIKE '%{0}%' ) ", para("WorkCenterID"))
            End If
        End If

        strSelect = String.Format(strSelect, para("FactoryID").ToString, sqlWhere)

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSelect
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取待派工工步详细（其他分厂可通用，暂时未启用，启用待优化）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerStepInfoDisDetail(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetContainerStepInfoDisDetail
        Dim strSelect As String =
            <string>
        SELECT distinct c.containerid,c.containername,c.containertype,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,
 C.MFGORDERNAME,C.PLANQTY,C.QTY as WIPQTY,C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, 
 C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE,C.TOGETHERFLAG,c.productname,c.productdescription
                     FROM containercurrentinfo c
                     LEFT JOIN (
                     SELECT s.stepinfoid,SUM(nvl(csd.qty,0)) disqty,c1.containerid,ws.workflowstepid
                     FROM containercurrentinfo c1
                     LEFT JOIN workflowstep ws ON ws.workflowstepid = c1.workflowstepid
                     LEFT JOIN stepinfo s ON s.workflowstepid = ws.workflowstepid
              LEFT JOIN containerstepdispatch2 csd ON csd.containerid = c1.containerid AND csd.workflowstepid = c1.workflowstepid AND csd.isuse = 0 AND csd.stepinfoid = s.stepinfoid     
                     WHERE s.stepinfoid IS NOT NULL and c1.originalfactoryid = '{0}' AND c1.isinstore = 0  AND c1.splitnum = 0 AND c1.containerstatus = 1
                     GROUP BY s.stepinfoid,c1.containerid,ws.workflowstepid
                     ) cc ON cc.containerid = c.containerid AND cc.workflowstepid = c.workflowstepid
                     LEFT JOIN containersplitinfo csi ON csi.splitcontainername = c.containername
                      WHERE  c.originalfactoryid = '{0}' AND c.isinstore = 0  AND c.splitnum = 0 AND c.containerstatus = 1
                      and nvl(cc.disqty,0) {1} c.qty
            </string>.Value

        strSelect = String.Format(strSelect, para("FactoryID").ToString, "<")
        strSelect += " AND c.mfgordername = '" + para("MfgOrderName") + "'"
        strSelect += " AND c.productid = '" + para("ProductID") + "'"
        strSelect += " AND c.workflowid = '" + para("WorkflowID") + "'"
        strSelect += " AND c.containerType = '" + para("ContainerType") + "'"
        strSelect += " AND c.workflowstepid = '" + para("WorkflowStepID") + "'"
        strSelect += " AND c.togetherflag = '" + para("TogatherFlag") + "'"
        strSelect += " AND c.containerno = '" + para("ContainerNo") + "'"
        strSelect += String.Format("  AND (c.WORKCENTERID='{0}' OR (c.TOWORKCENTERID='{0}' AND c.WORKCENTERDISPATCHNUM=0) OR (c.FROMWORKCENTERID='{0}' AND c.ISSHIP=1) or c.STEPWORKCENTERIDS  LIKE '%{0}%' ) ", para("WorkCenterID"))

        Return DatabaseHelper.Query_ReturnDatTable(strSelect)
    End Function

    ''' <summary>
    ''' 获取待派工工步详细（热表）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetRBContainerStepInfoDisDetail(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDispatchIBusiness.GetRBContainerStepInfoDisDetail
        Dim strSelect = String.Empty
        If Not String.IsNullOrWhiteSpace(para("ContainerID")) Then '如果不是合卡，则直接查询
            strSelect = String.Format(<string>                  
 SELECT c.containerid,c.containername,c.containertype,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,
 C.MFGORDERNAME,C.PLANQTY,C.QTY as WIPQTY,C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, 
 C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE,C.TOGETHERFLAG,c.productname,c.productdescription,cc.containername facontainername
                     FROM containercurrentinfo c
                     LEFT JOIN containersubcontainer csc ON csc.subcontainerid = c.containerid
                     LEFT JOIN container cc ON cc.containerid = csc.containerid
                     WHERE  c.containerid = '{0}'
                     AND NOT EXISTS(
                        SELECT 1 FROM containerstepdispatch csd WHERE csd.containerid = c.containerid AND csd.workflowstepid = c.workflowstepid AND csd.isuse = 0   
                      )
                      AND NOT EXISTS(
                      SELECT 1 FROM containercombinate cb WHERE cb.containerid = c.containerid 
                      )</string>.Value, para("ContainerID"))
        Else
            strSelect = String.Format(<string>                  
  SELECT c.containerid,c.containername,c.containertype,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,
 C.MFGORDERNAME,C.PLANQTY,C.QTY as WIPQTY,C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, 
 C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE,C.TOGETHERFLAG,c.productname,c.productdescription,cc.containername facontainername
                      FROM containercombinate cb
                      LEFT JOIN containercurrentinfo c ON c.containerid = cb.containerid
                      LEFT JOIN containersubcontainer csc ON csc.subcontainerid = c.containerid
                      LEFT JOIN container cc ON cc.containerid = csc.containerid
                      WHERE  cb.containercombinatename = '{0}' AND c.isinstore = 0  AND c.containerstatus = 1
                      AND NOT EXISTS(
                        SELECT 1 FROM containerstepdispatch csd WHERE csd.containerid = c.containerid AND csd.workflowstepid = c.workflowstepid AND csd.isuse = 0   
                      )</string>.Value, para("ProductBatch"))

        End If
        Return DatabaseHelper.Query_ReturnDatTable(strSelect)
    End Function

    ''' <summary>
    ''' 获取工序下的可派工工步数据
    ''' </summary>
    ''' <param name="workflowstepId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepInfoListByWorkflowStep(ByVal containerId As String, ByVal workflowstepId As String) As DataTable Implements uLMSDispatchIBusiness.GetStepInfoListByWorkflowStep
        Dim strSql As New StringBuilder
        strSql.AppendFormat(<string>
                     SELECT s.revision FROM containercurrentinfo cci
                     INNER JOIN containerstepdispatch2 csd ON csd.containerid = cci.containerid AND csd.workflowstepid = cci.workflowstepid AND csd.isuse = 0
                     INNER JOIN stepinfo s ON s.stepinfoid = csd.stepinfoid 
                     WHERE cci.containerid = '{0}' AND cci.workflowstepid = '{1}'
                               </string>.Value, containerId, workflowstepId)
        Dim dtRes = DatabaseHelper.Query_ReturnDatTable(strSql.ToString()) '查询出已派工的工步版本
        strSql.Clear()
        strSql.AppendLine(<string>
                     SELECT k.*,k.wipqty-k.disqty AS candisqty FROM(
                     SELECT s.stepinfoid,s.description stepinfodesc,s.stepseq,SUM(nvl(csd.qty,0)) disqty,c.containerid,ws.workflowstepid,c.qty AS wipqty
                     FROM containercurrentinfo c
                     LEFT JOIN workflowstep ws ON ws.workflowstepid = c.workflowstepid
                            </string>.Value)

        If dtRes.Rows.Count > 0 Then '如果存在已派工工步版本，则只查询该版本的工步，否则查询最新版本
            strSql.AppendFormat(<stirng>
                                 LEFT JOIN stepinfo s ON s.workflowstepid = ws.workflowstepid AND s.revision = '{0}'
                                 LEFT JOIN containerstepdispatch2 csd ON csd.containerid = c.containerid AND csd.workflowstepid = c.workflowstepid AND csd.isuse = 0 AND csd.stepinfoid = s.stepinfoid     
                                 WHERE s.stepinfoid IS NOT NULL AND c.containerid = '{1}' AND c.workflowstepid = '{2}'
                                 GROUP BY s.stepinfoid,s.description,s.stepseq,c.containerid,ws.workflowstepid,c.qty ) k
                                 WHERE k.disqty {3} wipqty
                                 ORDER BY k.stepseq
                                </stirng>.Value, dtRes.Rows(0)("revision").ToString(), containerId, workflowstepId, "<")
        Else
            strSql.AppendFormat(<string>
                     LEFT JOIN stepinfo s ON s.workflowstepid = ws.workflowstepid
                     INNER JOIN stepinfobase sb ON sb.revofrcdid = s.stepinfoid --取最新版本
                     LEFT JOIN containerstepdispatch2 csd ON csd.containerid = c.containerid AND csd.workflowstepid = c.workflowstepid AND csd.isuse = 0 AND csd.stepinfoid = s.stepinfoid     
                     WHERE s.stepinfoid IS NOT NULL AND c.containerid = '{0}' AND c.workflowstepid = '{1}'
                     GROUP BY s.stepinfoid,s.description,s.stepseq,c.containerid,ws.workflowstepid,c.qty ) k
                     WHERE k.disqty {2} wipqty
                     ORDER BY k.stepseq
                            </string>.Value, containerId, workflowstepId, "<")
        End If
        Return DatabaseHelper.Query_ReturnDatTable(strSql.ToString())
    End Function

    ''' <summary>
    ''' 填充工步派工
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function FillContainerStepInfoDispatchDataSql(ByVal entity As ContainerStepDispatchDTO) As Object() Implements IBusiness.uLMSDispatchIBusiness.FillContainerStepInfoDispatchDataSql
        Dim strsql As String
        Dim length As Integer = 0
        If entity.stepID Is Nothing Then
            strsql = "INSERT INTO CONTAINERSTEPDISPATCH2(CONTAINERID,FACTORYID,WORKCENTERID,TEAMID,WORKFLOWSTEPID,RESOURCEID,WORKERID,QTY,PLANNEDSTARTDATE,PLANNEDFINISHDATE,CONTAINERSTEPDISPATCHID,EMPLOYEEID,CREATEDATE,ISUSE,ISTRANSFER,PRIORITYCODE)"
            strsql += "VALUES(:containerid,:factoryid,:workcenterid,:teamid,:workflowstepid,:resourceid,:workerid,:qty,:plannedstartdate,:plannedfinishdate,:containerstepdispatchid,:employeeid,:createdate,:isuse,0,:PRIORITYCODE)"
            length = 14
        Else
            strsql = "INSERT INTO CONTAINERSTEPDISPATCH2(CONTAINERID,FACTORYID,WORKCENTERID,TEAMID,WORKFLOWSTEPID,RESOURCEID,WORKERID,QTY,PLANNEDSTARTDATE,PLANNEDFINISHDATE,CONTAINERSTEPDISPATCHID,EMPLOYEEID,CREATEDATE,ISUSE,ISTRANSFER,PRIORITYCODE,STEPINFOID)"
            strsql += "VALUES(:containerid,:factoryid,:workcenterid,:teamid,:workflowstepid,:resourceid,:workerid,:qty,:plannedstartdate,:plannedfinishdate,:containerstepdispatchid,:employeeid,:createdate,:isuse,0,:PRIORITYCODE,:STEPINFOID)"
            length = 15
        End If
        Dim insertParam(length) As OracleParameter
        'modify 2017/10/10
        insertParam(0) = DatabaseHelper.NewParameter(":containerid", entity.ContainerID, OracleType.VarChar)
        insertParam(1) = DatabaseHelper.NewParameter(":factoryid", entity.FactoryID, OracleType.VarChar)
        insertParam(2) = DatabaseHelper.NewParameter(":workcenterid", entity.WorkCenterID, OracleType.VarChar)
        insertParam(3) = DatabaseHelper.NewParameter(":teamid", entity.TeamID, OracleType.VarChar)
        If entity.ResourceID = "" Then
            insertParam(4) = DatabaseHelper.NewParameter(":resourceid", DBNull.Value, OracleType.VarChar)
        Else
            insertParam(4) = DatabaseHelper.NewParameter(":resourceid", entity.ResourceID, OracleType.VarChar)
        End If
        insertParam(5) = DatabaseHelper.NewParameter(":workerid", entity.WorkerID, OracleType.VarChar)
        insertParam(6) = DatabaseHelper.NewParameter(":qty", entity.Qty, OracleType.Number)
        If entity.PlannedStartDate = DateTime.Parse("0001/01/01") Then
            insertParam(7) = DatabaseHelper.NewParameter(":plannedstartdate", DBNull.Value, OracleType.DateTime)
        Else
            insertParam(7) = DatabaseHelper.NewParameter(":plannedstartdate", entity.PlannedStartDate, OracleType.DateTime)
        End If
        If entity.PlannedFinishDate = DateTime.Parse("0001/01/01") Then
            insertParam(8) = DatabaseHelper.NewParameter(":plannedfinishdate", DBNull.Value, OracleType.DateTime)
        Else
            insertParam(8) = DatabaseHelper.NewParameter(":plannedfinishdate", entity.PlannedFinishDate, OracleType.DateTime)
        End If

        insertParam(9) = DatabaseHelper.NewParameter(":containerstepdispatchid", entity.ContainerStepDispatchID, OracleType.VarChar)
        insertParam(10) = DatabaseHelper.NewParameter(":employeeid", entity.EmployeeID, OracleType.VarChar)
        insertParam(11) = DatabaseHelper.NewParameter(":createdate", System.DateTime.Now, OracleType.DateTime)
        'Modify by Yuxj20180628
        'insertParam(12) = DatabaseHelper.NewParameter(":workflowstepid", entity.WorkflowstepID, OracleType.VarChar)
        insertParam(12) = DatabaseHelper.NewParameter(":workflowstepid",
                                                     IIf(entity.WorkflowstepID Is Nothing, String.Empty, entity.WorkflowstepID), OracleType.VarChar)

        insertParam(13) = DatabaseHelper.NewParameter(":Isuse", 0, OracleType.Number)
        insertParam(14) = DatabaseHelper.NewParameter(":PRIORITYCODE", entity.PriorityCode, OracleType.Number)
        If length = 15 Then
            insertParam(15) = DatabaseHelper.NewParameter(":STEPINFOID", entity.stepID, OracleType.VarChar)
        End If
        Return New Object() {strsql, insertParam}
    End Function

    ''' <summary>
    ''' 填充工步派工多派工工人数据
    ''' </summary>
    ''' <param name="ContainerStepDispatchID"></param>
    ''' <param name="Workerid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function FillStepInfoDispatchDetails(ByVal ContainerStepDispatchID As String, ByVal Workerid As String) As Object() Implements IBusiness.uLMSDispatchIBusiness.FillStepInfoDispatchDetails
        Dim strsql As New StringBuilder
        'add:Wangjh 20190403增加重复记录判断
        'strsql.AppendFormat("select *  from CONTAINERSTEPDISPATCHDETAIL where CONTAINERSTEPDISPATCHID='{0}' and WORKERID='{1}'", ContainerStepDispatchID, Workerid)
        'If DatabaseHelper.Query_ReturnDatTable(strsql.ToString).Rows.Count > 0 Then '已有
        '    Return f
        'End If
        '

        'add:Wangjh 20220608增加重复记录处理
        strsql.Clear()
        strsql.AppendLine(<string> begin delete CONTAINERSTEPDISPATCHDETAIL2 where CONTAINERSTEPDISPATCHID=:CONTAINERSTEPDISPATCHID and WORKERID=:WORKERID;
INSERT INTO CONTAINERSTEPDISPATCHDETAIL2(CONTAINERSTEPDISPATCHID,WORKERID,CreateDate) VALUES(:CONTAINERSTEPDISPATCHID,:WORKERID,sysdate); end;</string>.Value)
        'modify 2017/10/10
        Dim newpar(1) As OracleParameter
        newpar(0) = DatabaseHelper.NewParameter(":CONTAINERSTEPDISPATCHID", ContainerStepDispatchID, OracleType.VarChar)
        newpar(1) = DatabaseHelper.NewParameter(":WORKERID", Workerid, OracleType.VarChar)
        Return New Object() {strsql.ToString, newpar}

    End Function

    ''' <summary>
    ''' 更新批次派工记录的状态（工步）
    ''' </summary>
    ''' <param name="ContainerStepDispatchIDs"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateDispatch2IsCompleteStatus(ByVal ContainerStepDispatchIDs As String) As Boolean Implements uLMSDispatchIBusiness.UpdateDispatch2IsCompleteStatus
        SyncLock threadLock
            UpdateDispatch2IsCompleteStatus = False
            Dim strsql As New StringBuilder
            Dim str_Value As String =
                <string>
               SELECT CSD.CONTAINERSTEPDISPATCHID, NVL(CSD.ISCOMPLETE, 0) ISCOMPLETE
	           FROM CONTAINERSTEPDISPATCH2 CSD
               LEFT JOIN (SELECT NVL(SUM(CSW.QTY), 0) REPORTTOTALQTY,CSW.CONTAINERSTEPDISPATCHID
							    FROM CONTAINERSTEPWORKREPORT2 CSW
            </string>
            strsql.AppendLine(str_Value)
            strsql.AppendFormat(" WHERE ({0})", ContainerStepDispatchIDs)
            str_Value =
                <string>
                                AND CSW.ISUSE = 0 GROUP BY CSW.CONTAINERSTEPDISPATCHID) DISCSW
		        ON DISCSW.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID
                WHERE ((CSD.QTY > DISCSW.REPORTTOTALQTY AND NVL(CSD.ISCOMPLETE, 0) = 1)
                OR (DISCSW.CONTAINERSTEPDISPATCHID IS NULL AND NVL(CSD.ISCOMPLETE,0) = 1)
            </string>
            strsql.AppendLine(str_Value)
            strsql.AppendLine("OR (CSD.QTY <= DISCSW.REPORTTOTALQTY AND NVL(CSD.ISCOMPLETE, 0) = 0))")
            strsql.AppendFormat(" AND ({0})", ContainerStepDispatchIDs.Replace("csw", "csd"))
            Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
            If dt.Rows.Count > 0 Then
                Dim SQLStringList As New Dictionary(Of String, OracleParameter())
                Dim sqlValue As String = ""
                For Each dr As DataRow In dt.Rows
                    sqlValue = "UPDATE CONTAINERSTEPDISPATCH2 CSD SET "
                    If Integer.Parse(dr("ISCOMPLETE")) = 1 Then
                        sqlValue += " CSD.ISCOMPLETE = 0 "
                    Else
                        sqlValue += " CSD.ISCOMPLETE = 1 "
                    End If
                    sqlValue += String.Format(" WHERE CSD.CONTAINERSTEPDISPATCHID = '{0}'", dr("CONTAINERSTEPDISPATCHID"))
                    SQLStringList.Add(sqlValue, Nothing)
                Next
                DatabaseHelper.ExecuteSqlTranByHash(SQLStringList)
            End If
            UpdateDispatch2IsCompleteStatus = True
        End SyncLock
    End Function

    ''' <summary>
    ''' 验证批次在制工序下的工步信息
    ''' </summary>
    ''' <param name="containerId"></param>
    ''' <param name="workflowstepId"></param>
    ''' <param name="msg"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsVaildSeqForStepInfo(ByVal containerId As String, ByVal workflowstepId As String, ByRef msg As String) As Boolean Implements uLMSDispatchIBusiness.IsVaildSeqForStepInfo
        Dim strSql = <string>
                     SELECT s.stepinfoid,sb.stepinfoname,s.stepseq
                     FROM containercurrentinfo c
                     LEFT JOIN workflowstep ws ON ws.workflowstepid = c.workflowstepid
                     LEFT JOIN stepinfo s ON s.workflowstepid = ws.workflowstepid
                     LEFT JOIN stepinfobase sb ON sb.stepinfobaseid = s.stepinfobaseid
                     WHERE s.stepinfoid IS NOT NULL AND c.containerid = '{0}' AND c.workflowstepid = '{1}'
                     ORDER BY s.stepseq                         
                     </string>.Value
        strSql = String.Format(strSql, containerId, workflowstepId)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dt.Rows.Count = 0 Then
            msg = "在制工序没有工步信息"
            Return False
            Exit Function
        End If

        If dt.AsEnumerable().Where(Function(p) String.IsNullOrWhiteSpace(p("stepseq").ToString())).Count() > 0 Then
            msg = "在制工序下的工步存在顺序号为空的情况"
            Return False
            Exit Function
        End If

        Dim dt1 As DataTable = dt.DefaultView.ToTable(True, "stepseq")
        '如果存在相同的工步顺序号，则提示
        If dt1.Rows.Count <> dt.Rows.Count Then
            msg = "在制工序下的工步存在顺序号相同的情况"
            Return False
            Exit Function
        End If
        '两种类型的批次进行验证
        strSql = String.Format("SELECT 1 FROM containersubcontainer csc WHERE csc.subcontainerid = '{0}'", containerId)
        If DatabaseHelper.GetSingle(String.Format(strSql, containerId)) Is Nothing Then '无订单批次验证
            strSql = <string>
                         SELECT 1 FROM rbordercontainerreceive r 
                         WHERE r.containerid = '{0}' AND r.receivedate IS NOT NULL
                     </string>.Value
            If DatabaseHelper.GetSingle(String.Format(strSql, containerId)) Is Nothing Then
                msg = "该无订单批次未接收，请接收后再操作"
                Return False
                Exit Function
            End If
        Else  '转工子卡验证
            '如果批次未转工接收，则提示
            strSql = <string>SELECT 1 FROM containersubcontainer csc
            LEFT JOIN containercurrentinfo c ON c.containerid = csc.containerid 
            LEFT JOIN containershiprecord csr ON csr.containerid = c.containerid AND csr.isuse = 0 AND csr.toacceptdate IS NOT NULL
            LEFT JOIN containershiprecordstep csrs ON csrs.containershiprecordid = csr.containershiprecordid 
            LEFT JOIN workflowstep ws ON ws.workflowstepid = csrs.workflowstepid 
            WHERE csc.subcontainerid = '{0}' AND (ws.workflowstepid = csc.workflowstepid OR ws.workflowstepname = csc.workflowstepname)
                 </string>.Value
            If DatabaseHelper.GetSingle(String.Format(strSql, containerId)) Is Nothing Then
                msg = "该序未转工接收，请完成转工接收后再操作"
                Return False
                Exit Function
            End If
            '查询子卡对应的父卡当前序是否在子卡对应的父工序上（只针对转工序批次）
            strSql = <string>
            SELECT c.workflowstepname currstepname,csc.workflowstepname shipstepname,nvl(u.attributevalue,0) timeEffective
            FROM containersubcontainer csc
            LEFT JOIN containercurrentinfo c ON c.containerid = csc.containerid 
            LEFT JOIN Userattribute u ON u.parentid = csc.subcontainerid AND u.userattributename = '热表时效性'
            WHERE csc.subcontainerid = '{0}'
                 </string>.Value
            Dim dtsub = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, containerId))
            If dtsub.Rows(0)("currstepname").ToString() <> dtsub.Rows(0)("shipstepname").ToString() Then
                '如果没有时效性，则不可以提前派工
                If dtsub.Rows(0)("timeEffective").ToString() = "0" Then
                    msg = "主卡在制工序未在该子卡转工序上，请先完成主卡其他序，再操作"
                    Return False
                    Exit Function
                End If
            End If
        End If
        Return True
    End Function

    ''' <summary>
    ''' 验证工序是否所有工步派工完成
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsStepInfoDispatched(ByVal para As DataRow) As Boolean Implements uLMSDispatchIBusiness.IsStepInfoDispatched
        Dim strSql = String.Format(<string>
                     SELECT s1.revision FROM containerstepdispatch2 csd 
                     LEFT JOIN stepinfo s1 ON s1.stepinfoid = csd.stepinfoid
                     WHERE csd.containerid = '{0}' AND csd.workflowstepid = '{1}' AND csd.isuse = 0
                 </string>.Value, para("ContainerID").ToString, para("WORKFLOWSTEPID").ToString)
        Dim dtRes = DatabaseHelper.Query_ReturnDatTable(strSql)

        If dtRes.Rows.Count = 0 Then
            Return False
        End If

        strSql = <string>
                    SELECT SUM(NVL(csd.qty,0)) disqty FROM stepinfo s 
                    LEFT JOIN containerstepdispatch2 csd ON csd.stepinfoid = s.stepinfoid AND csd.containerid = '{0}' AND csd.workflowstepid = '{1}' AND csd.isuse = 0
                    WHERE s.workflowstepid = '{1}' AND s.revision = '{2}'
                    GROUP BY s.stepinfoid
                 </string>.Value

        strSql = String.Format(strSql, para("ContainerID").ToString, para("WORKFLOWSTEPID").ToString, dtRes.Rows(0)("revision").ToString())

        Dim wipQty = Integer.Parse(para("WipQty").ToString)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        '如果存在小于在制数的，则表示工序未派工完成
        If dt.AsEnumerable().Where(Function(p) Integer.Parse(p("disqty").ToString) < wipQty).Count > 0 Then
            Return False
        End If
        Return True
    End Function

    ''' <summary>
    ''' 获取可删除工步派工 add by zhangrj 2022-09-20
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepInfoDeletableDispatchInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSDispatchIBusiness.GetStepInfoDeletableDispatchInfo
        Dim strSelect = <string>
                        SELECT csd.containerstepdispatchid,cci.containername,cci.containerid,cci.qty wipqty, s.description, csd.qty disqty,
nvl(wm_concat(e1.fullname),e.fullname) workername,e2.fullname dispatchername,ws.workflowstepname,ws.workflowstepid
FROM containerstepdispatch2 csd
LEFT JOIN containerstepworkreport2 csw ON csw.containerstepdispatchid = csd.containerstepdispatchid AND csw.isuse = 0
LEFT JOIN employee e ON e.employeeid = csd.workerid
LEFT JOIN containercurrentinfo cci ON cci.containerid = csd.containerid
LEFT JOIN stepinfo s ON s.stepinfoid = csd.stepinfoid
LEFT JOIN containerstepdispatchdetail2 csdd ON csdd.containerstepdispatchid = csd.containerstepdispatchid
LEFT JOIN employee e1 ON e1.employeeid = csdd.workerid
LEFT JOIN employee e2 ON e2.employeeid = csd.employeeid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = s.workflowstepid
WHERE csd.containerid IN ({0}) and cci.containerstatus = 1
AND csd.isuse = 0 
AND csw.containerstepworkreportid IS NULL
AND NOT EXISTS(SELECT 1 FROM containercombinate cb WHERE cb.containerid = csd.containerid)
GROUP BY csd.containerstepdispatchid,e.fullname,e2.fullname,cci.containername,cci.containerid,cci.qty,ws.workflowstepname,ws.workflowstepid,s.description,csd.qty
                        </string>.Value
        strSelect = String.Format(strSelect, para("ContainerIDs").ToString)
        Dim result As New uMESProcessResult
        result.Result = True
        result.DBTable = DatabaseHelper.Query_ReturnDatTable(strSelect)
        Return result
        'Dim paraPage As New uMESPagingDataDTO
        'paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        'paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        'paraPage.strSQL = strSelect
        'Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 删除工步派工 add by zhangrj 2022-09-20
    ''' </summary>
    ''' <param name="containerStepDispatchID"></param>
    ''' <param name="list"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DelContainerStepDispatch2Bycsdid(ByVal containerStepDispatchID As String, ByVal list As List(Of Dictionary(Of String, String)), ByRef msg As String) As Boolean Implements IBusiness.uLMSDispatchIBusiness.DelContainerStepDispatch2Bycsdid
        Dim strsql As String = <string>
SELECT csd.containerstepdispatchid FROM containerstepdispatch2 csd
LEFT JOIN containerstepworkreport2 csw ON csw.containerstepdispatchid = csd.containerstepdispatchid AND csw.isuse = 0
WHERE csd.isuse = 0 AND csw.containerstepworkreportid IS NOT NULL AND csd.containerstepdispatchid IN ({0})
                         </string>.Value
        strsql = String.Format(strsql, containerStepDispatchID)
        Dim dt = DatabaseHelper.Query_ReturnDatTable(strsql)
        If dt.Rows.Count > 0 Then '如果派工已经报工，则不更新
            msg = "存在已报工的派工，请撤销报工后刷新页面"
            Return False
            Exit Function
        End If
        Dim sqlList As New ArrayList
        strsql = String.Format("UPDATE CONTAINERSTEPDISPATCH2 C SET C.ISUSE=1 WHERE C.CONTAINERSTEPDISPATCHID in ({0})", containerStepDispatchID)
        sqlList.Add(strsql)
        For Each dic As Dictionary(Of String, String) In list
            '删除工序派工
            strsql = String.Format("UPDATE CONTAINERSTEPDISPATCH CSD SET CSD.ISUSE=1 WHERE CSD.CONTAINERID='{0}' and CSD.Workflowstepid = '{1}' and CSD.ISUSE = 0", dic("CONTAINERID"), dic("WORKFLOWSTEPID"))
            sqlList.Add(strsql)
        Next
        DatabaseHelper.ExecuteSqlTran(sqlList)
        msg = "删除成功"
        Return True
    End Function

    ''' <summary>
    ''' 删除工步派工（通过工序） add by zhangrj 2023-3-7
    ''' </summary>
    ''' <param name="list"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DelContainerStepDispatch2BySpec(ByVal list As List(Of Dictionary(Of String, String))) As Boolean Implements IBusiness.uLMSDispatchIBusiness.DelContainerStepDispatch2BySpec
        Dim sqlList As New ArrayList
        For Each dic As Dictionary(Of String, String) In list
            Dim strsql1 As New StringBuilder
            Dim querySql = String.Format("select csd.containerstepdispatchid from containerstepdispatch2 csd where csd.containerid = '{0}' and csd.workflowstepid='{1}' and csd.isuse = 0", dic("CONTAINERID"), dic("WORKFLOWSTEPID"))
            Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(querySql)
            '如果不存在工步级的派工数据，则跳过（不跳过会删除工序所有派工记录）
            If dt.Rows.Count = 0 Then
                Continue For
            End If
            '查询工步的报工记录，用于删除其报工记录和实测值
            querySql = String.Format("select csw.stepinfoid,csw.containerstepworkreportid from containerstepworkreport2 csw where csw.containerid = '{0}' and csw.workflowstepid='{1}' and csw.isuse = 0", dic("CONTAINERID"), dic("WORKFLOWSTEPID"))
            Dim dtReport As DataTable = DatabaseHelper.Query_ReturnDatTable(querySql)
            For Each dr As DataRow In dtReport.Rows
                '删除实测值
                strsql1.Clear()
                strsql1.AppendFormat("delete stepcheckpointresult sc WHERE sc.containerstepworkreportid='{0}' AND sc.containerid='{1}'", dr("ContainerStepWorkReportID"), dic("CONTAINERID"))
                strsql1.AppendFormat("  and sc.stepcheckpointid IN (select st.stepcheckpointid from stepcheckpoint st where st.workflowstepid='{0}' and st.stepinfoid='{1}') ", dic("WORKFLOWSTEPID"), dr("StepInfoID"))
                If Not sqlList.Contains(strsql1) Then
                    sqlList.Add(strsql1.ToString)
                End If
            Next

            '删除工步报工（工序下所有）
            strsql1.Clear()
            strsql1.AppendFormat("UPDATE containerstepworkreport2 csr SET csr.ISUSE=1 WHERE csr.containerid = '{0}' and csr.workflowstepid='{1}' and csr.isuse = 0", dic("CONTAINERID"), dic("WORKFLOWSTEPID"))
            If Not sqlList.Contains(strsql1) Then
                sqlList.Add(strsql1.ToString)
            End If

            '删除工步派工（工序下所有）
            strsql1.Clear()
            strsql1.AppendFormat("UPDATE containerstepdispatch2 csd SET csd.isuse =1 WHERE csd.containerid = '{0}' AND csd.workflowstepid ='{1}' AND csd.isuse = 0", dic("CONTAINERID"), dic("WORKFLOWSTEPID"))
            If Not sqlList.Contains(strsql1) Then
                sqlList.Add(strsql1.ToString)
            End If

            '将工步派工撤销数据推送到数据中心
            sqlList.AddRange(m_CommonBusiness.InsertSyncTableChangeRecode(dt, "containerstepdispatch2", "containerstepdispatchid", toAppID_RB, 2, False))

            '删除工序派工
            strsql1.Clear()
            strsql1.AppendLine("UPDATE containerstepdispatch C SET C.ISUSE=1 ")
            strsql1.AppendFormat(" WHERE C.CONTAINERID='{0}' and C.Workflowstepid = '{1}' and C.ISUSE=0", dic("CONTAINERID"), dic("WORKFLOWSTEPID"))
            If Not sqlList.Contains(strsql1) Then
                sqlList.Add(strsql1.ToString)
            End If
        Next
        If sqlList.Count > 0 Then
            DatabaseHelper.ExecuteSqlTran(sqlList)
        End If
        Return True
    End Function
#End Region

    ''' <summary>
    ''' 获取派工到工区的派工信息 Yuxj20221201
    ''' 优化sql
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetZSWorkCenterDispatch(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSDispatchIBusiness.GetZSWorkCenterDispatch
        Dim strsql As String =
            <string>
                    SELECT ep.enginepartplanid,
                           me.engineproductid mfgproductid,
                           ep.MfgorderName,
                           ep.machineno MachineName,
                           ep.containerno,
                           ep.containerid,
                           ep.workflowname || ':' || ep.workflowver workflowname,
                           ep.productname,
                           ep.productdesc productdesc,
                           ep.workflowdesc workflowdesc,
                           ep.workflowstepname,
                           w.workcentername,
                           w.workcenterid,
                           ep.plannedstartdate,
                           ep.plannedfinishdate,
                           ep.CompleteStepId
                      FROM enginepartplan ep
                      left join mfg_engine_data me on me.mfgorderid=ep.mfgorderid
                      LEFT JOIN workcenter w ON w.workcenterid = ep.workcenterid  
                     WHERE ep.workflowstepid > '0'
                           AND ep.containerno > '0'
                       and ep.usecompleted = '0' AND (instr(nvl(ep.workflowstepname,'0'), '返件和故检') = 0)
                       and nvl(ep.concontainerid,'0')!='0'  
                </string>

        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        If para.ContainsKey("ContainerNo") Then
            sb.AppendFormat(" and ep.containerno like '%{0}%' ", para("ContainerNo"))
        End If

        If para.ContainsKey("MachineStart") Then
            sb.AppendFormat(" and ep.machineno like '%{0}%' ", para("MachineStart"))
        End If

        If para.ContainsKey("MfgorderName") Then
            sb.AppendFormat(" and ep.MfgorderName like '%{0}%' ", para("MfgorderName"))
        End If

        If para("Status") = "1" Then
            sb.AppendFormat(" and ep.workcenterid >'0' ")
        Else
            sb.AppendFormat(" and ep.workcenterid is null ")
        End If
        sb.AppendLine(" order by ep.MfgorderName,ep.workflowstepid asc")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
    ''' <summary>
    ''' 查询工区已派的班 yuxj2022优化sql
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetZSWorkCenterShift(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSDispatchIBusiness.GetZSWorkCenterShift
        Dim strsql As String =
            <string>
                    SELECT CTD.CONTAINERTEAMDISPATCHID,
                           me.engineproductid MFGPRODUCTID,
                           EP.CONTAINERNAME,
                           ep.concontainerid CONTAINERID,
                           ep.MFGORDERNAME,
                           ep.machineno MACHINENAME,
                           ep.PRODUCTID,
                           ep.productdesc PRODUCTDESC,
                           ep.productname,
                           ep.productver PRODUCTREV,
                           EP.CONTAINERNO TOGETHERFLAG,
                           ST.SHIFTNAME,
                           ST.SHIFTID,
                           T.TEAMNAME,
                           T.TEAMID,
                           me.engineproductname MACHINEPNAME,
                           EP.PLANNEDSTARTDATE PLANNEDSTARTDATE,
                           EP.PLANNEDFINISHDATE PLANNEDCOMPLETIONDATE,
                           ep.workflowname||':'||ep.workflowver WORKFLOWNAME,
                           ep.workflowdesc WORKFLOWDESC,
                           ep.workflowstepname MACHINESTEPNAME,
                           ep.workflowstepid MACHINESTEPID,
                           EP.WORKFLOWID,
                           EP.ISROUTINETEST
                      FROM ENGINEPARTPLAN EP
                      LEFT JOIN mfg_engine_data me on me.mfgorderid=ep.mfgorderid                     
                      LEFT JOIN (SELECT * FROM CONTAINERTEAMDISPATCH CTD WHERE CTD.FACTORYID='{0}'  AND CTD.ISUSE = 0) CTD
                        ON CTD.CONTAINERID = ep.concontainerid    
                      LEFT JOIN SHIFT ST
                        ON ST.SHIFTID = CTD.SHIFTID
                      LEFT JOIN TEAM T
                        ON T.TEAMID = CTD.TEAMID        
                     WHERE EP.USECOMPLETED = '0'
                       AND EP.CONTAINERNO > '0'
                       AND INSTR(nvl(ep.workflowstepname,'0'), '返件和故检') = 0
                       AND EP.TYPE = 1 and nvl(ep.concontainerid,'0')!='0'      
                </string>
        strsql = String.Format(strsql, para("FactoryID"))
        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        If para.Keys.Contains("ContainerNo") Then
            sb.AppendFormat(" AND ep.containerno like '%{0}%' ", para("ContainerNo"))
        End If

        If para.ContainsKey("MachineStatus") Then
            sb.AppendFormat(" AND ep.workflowstepid='{0}' ", para("MachineStatus"))
        End If
        If para.ContainsKey("ProductName") Then
            sb.AppendFormat(" AND ep.productname LIKE '%{0}%' ", para("ProductName"))
        End If
        If para.ContainsKey("MfgOrderName") Then
            sb.AppendFormat(" AND ep.mfgordername like '%{0}%' ", para("MfgOrderName"))
        End If
        sb.AppendFormat(" and ep.workcenterid='{0}' ", para("WorkCenterID")) 'ep.workcenterid->ctd.workcenterid yuxj20190614

        If para.ContainsKey("MachineStart") Then
            If para.ContainsKey("MachineEnd") Then
                sb.AppendFormat(" AND ep.machineno between  '{0}' and '{1}' ", para("MachineStart"), para("MachineEnd"))
            Else
                sb.AppendFormat(" AND ep.machineno like '%{0}%' ", para("MachineStart"))
            End If
        End If
        If para.ContainsKey("Role") Then
            sb.AppendLine(" and ep.workflowstepid is not null ")
            If para("Role") = "Shift" Then
                If para("ContainerStatus") = "1" Then
                    sb.AppendFormat(" AND ctd.workcenterid='{0}' ", para("WorkCenterID"))
                    sb.AppendLine(" AND ctd.shiftid >'0' ")
                Else
                    sb.AppendLine(" AND CTD.CONTAINERTEAMDISPATCHID is null ")
                End If
            ElseIf para("Role") = "Team" Then
                sb.AppendFormat(" AND ctd.workcenterid='{0}' ", para("WorkCenterID"))
                sb.AppendFormat(" AND st.ShiftID='{0}' ", para("ShiftID"))

                If para("ContainerStatus") = "1" Then
                    sb.AppendLine(" AND ctd.teamid >'0' ")
                ElseIf para("ContainerStatus") = "0" Then
                    sb.AppendLine(" AND ctd.teamid IS NULL ")
                End If
            End If
        End If
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取装试的批次信息 create tianFW 2017年11月30日
    ''' Modify by Yuxj2018年0929 获取发动机件号Id(订单件号Id)
    ''' yuxj2022 优化sql
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetZSContainerInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSDispatchIBusiness.GetZSContainerInfo
        Dim strsql As String =
                <string>
                select ep.containername,
                       ep.concontainerid containerid,
                       ep.mfgordername,
                       ep.machineno machinename,
                       ep.productid,
                       ep.productdesc productdesc,
                       ep.productname,
                       ep.productver productrev,
                       me.productfamilyname familylistnames,
                       ep.containerno togetherflag,
                       st.shiftname,
                       st.shiftid,
                       t.teamname,
                       t.teamid,
                       ep.plannedstartdate plannedstartdate,
                       ep.plannedfinishdate plannedcompletiondate,
                       ep.workflowname || ':' || ep.workflowver workflowname,
                       ep.workflowdesc workflowdesc,
                       ep.workflowstepname machinestepname,
                       ep.workflowstepid machinestepid,
                       ep.workflowid,
                       ep.isroutinetest
                  FROM (SELECT containerid, shiftid, teamid, workcenterid
                          FROM containerteamdispatch
                         WHERE Isuse = '0'
                           and factoryid = '{0}'
                         GROUP BY containerid, shiftid, teamid, workcenterid) ctd
                 INNER JOIN enginepartplan ep ON ep.concontainerid =
                                                 ctd.containerid
                  left join mfg_engine_data me on me.mfgorderid = ep.mfgorderid
                  LEFT JOIN shift st ON st.shiftid = ctd.shiftid
                  LEFT JOIN team t ON t.teamid = ctd.teamid
                  /*LEFT JOIN (SELECT SUM(orgstepnum) orgstepnum,
                                    SUM(csdstepnum) csdstepnum,
                                    containerid,
                                    workflowid,
                                    wftype,
                                    orgteamid
                               FROM enginepartplanStepDispatchInfo
                              GROUP BY containerid, workflowid, wftype, orgteamid) epd ON epd.containerid =ep.concontainerid
                                                                                      AND epd.workflowid =
                                                                                          ep.workflowid*/
                 WHERE ep.usecompleted = '0' and nvl(ep.concontainerid,'0')!='0'   
            </string>
        strsql = String.Format(strsql, para("FactoryID"))
        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        If para.ContainsKey("TeamID") Then
            sb.AppendFormat(" AND ctd.teamid = '{0}'", para("TeamID"))
            'sb.AppendFormat(" and DECODE(epd.wftype,1,epd.orgteamid,'{0}')='{0}' ", para("TeamID"))
        End If
        If para.ContainsKey("ShiftID") Then
            sb.AppendFormat(" AND ctd.ShiftID = '{0}'", para("ShiftID"))
        End If
        'If para.ContainsKey("DispatchStatus") Then
        '    If para("DispatchStatus") = "1" Then '未派工
        '        sb.AppendLine(" and epd.csdstepnum=0 ")
        '    ElseIf para("DispatchStatus") = "2" Then '未派完
        '        sb.AppendLine(" and epd.csdstepnum>0 and epd.csdstepnum<epd.orgstepnum ")
        '    ElseIf para("DispatchStatus") = "3" Then '已派完
        '        sb.AppendLine(" and epd.csdstepnum>=epd.orgstepnum ")
        '    End If
        'End If

        If para.ContainsKey("DispatchStatus") Then
            If para("DispatchStatus") = "1" Then '未派工
                sb.AppendLine(" and ep.stepdispatchcount=0 ")
            ElseIf para("DispatchStatus") = "2" Then '未派完
                sb.AppendLine(" and ep.stepdispatchcount>0 and ep.stepdispatchcount<ep.workflowstepcount ")
            ElseIf para("DispatchStatus") = "3" Then '已派完
                sb.AppendLine(" and ep.workflowstepcount>0 and ep.stepdispatchcount>=ep.workflowstepcount ")
            End If
        End If

        If para.Keys.Contains("ContainerNo") Then
            sb.AppendFormat(" AND ep.containerno like '%{0}%' ", para("ContainerNo"))
        End If

        If para.ContainsKey("MachineStatus") Then
            sb.AppendFormat(" AND ep.workflowstepid='{0}' ", para("MachineStatus"))
        End If
        If para.ContainsKey("ProductName") Then
            sb.AppendFormat(" AND ep.productname LIKE '%{0}%' ", para("ProductName"))
        End If
        If para.ContainsKey("MfgOrderName") Then
            sb.AppendFormat(" AND ep.mfgordername like '%{0}%' ", para("MfgOrderName"))
        End If

        sb.AppendLine(" and instr(nvl(ep.workflowstepname,'0'),'返件和故检')=0 ")
        sb.AppendFormat(" and (ctd.workcenterid='{0}' or ep.workcenterid='{0}') ", para("WorkCenterID"))
        If para.ContainsKey("MachineStart") Then
            If para.ContainsKey("MachineEnd") Then
                sb.AppendFormat(" AND ep.machineno between  '{0}' and '{1}' ", para("MachineStart"), para("MachineEnd"))
            Else
                sb.AppendFormat(" AND ep.machineno like '%{0}%' ", para("MachineStart"))
            End If
        End If

        sb.AppendLine(" order by ep.workflowstepid ,ep.containerno asc")

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)

    End Function
    ''' <summary>
    ''' add by longb 2023-06-12
    ''' 获取装试的批次信息(排产下发-班组长派工)
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetNewZSContainerInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSDispatchIBusiness.GetNewZSContainerInfo
        Dim strsql As String =
                <string>
                select distinct ep.containername,
                       ep.concontainerid containerid,
                       ep.mfgordername,
                       ep.machineno machinename,
                       ep.productid,
                       ep.productdesc productdesc,
                       ep.productname,
                       ep.productver productrev,
                       me.productfamilyname familylistnames,
                       ep.containerno togetherflag,
                       st.shiftname,
                       st.shiftid,
                       --t.teamname,
                       --t.teamid,
                       ep.plannedstartdate plannedstartdate,
                       ep.plannedfinishdate plannedcompletiondate,
                       ep.workflowname || ':' || ep.workflowver workflowname,
                       ep.workflowdesc workflowdesc,
                       ep.workflowstepname machinestepname,
                       ep.workflowstepid machinestepid,
                       ep.workflowid,
                       ep.isroutinetest
                  FROM (SELECT containerid, shiftid, teamid, workcenterid
                          FROM containerteamdispatch
                         WHERE Isuse = '0'
                           and factoryid = '{0}'
                         GROUP BY containerid, shiftid, teamid, workcenterid) ctd
                 INNER JOIN enginepartplan ep ON ep.concontainerid =
                                                 ctd.containerid
                  left join mfg_engine_data me on me.mfgorderid = ep.mfgorderid
                  LEFT JOIN shift st ON st.shiftid = ctd.shiftid
                  LEFT JOIN team t ON t.teamid = ctd.teamid
                 WHERE ep.usecompleted = '0' and nvl(ep.concontainerid,'0')!='0'    
            </string>
        strsql = String.Format(strsql, para("FactoryID"))
        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        If para.ContainsKey("TeamID") Then
            sb.AppendFormat(" AND ctd.teamid = '{0}'", para("TeamID"))
        End If
        If para.ContainsKey("ShiftID") Then
            sb.AppendFormat(" AND ctd.ShiftID = '{0}'", para("ShiftID"))
        End If
        If para.ContainsKey("DispatchStatus") Then
            If para("DispatchStatus") = "1" Then '未派工
                sb.AppendLine(" and nvl(ep.stepdispatchcount,0)=0 ")
            ElseIf para("DispatchStatus") = "2" Then '已派工(未派完)
                sb.AppendLine(" and nvl(ep.stepdispatchcount,0)>0 ") 'and nvl(ep.stepdispatchcount<nvl(ep.workflowstepcount,0) 
            ElseIf para("DispatchStatus") = "3" Then '已派完
                sb.AppendLine(" and nvl(ep.workflowstepcount,0)>0 and nvl(ep.stepdispatchcount>=nvl(ep.workflowstepcount,0) ")
            End If
        End If

        If para.Keys.Contains("ContainerNo") Then
            sb.AppendFormat(" AND ep.containerno like '%{0}%' ", para("ContainerNo"))
        End If

        If para.ContainsKey("MachineStatus") Then
            sb.AppendFormat(" AND ep.workflowstepid='{0}' ", para("MachineStatus"))
        End If
        If para.ContainsKey("ProductName") Then
            sb.AppendFormat(" AND ep.productname LIKE '%{0}%' ", para("ProductName"))
        End If
        If para.ContainsKey("MfgOrderName") Then
            sb.AppendFormat(" AND ep.mfgordername like '%{0}%' ", para("MfgOrderName"))
        End If

        sb.AppendLine(" and instr(nvl(ep.workflowstepname,'0'),'返件和故检')=0 ")
        sb.AppendFormat(" and (ctd.workcenterid='{0}' or ep.workcenterid='{0}') ", para("WorkCenterID"))
        If para.ContainsKey("MachineStart") Then
            If para.ContainsKey("MachineEnd") Then
                sb.AppendFormat(" AND ep.machineno between  '{0}' and '{1}' ", para("MachineStart"), para("MachineEnd"))
            Else
                sb.AppendFormat(" AND ep.machineno like '%{0}%' ", para("MachineStart"))
            End If
        End If

        sb.AppendLine(" order by ep.workflowstepid ,ep.containerno asc")

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)

    End Function

    ''' <summary>
    ''' yuxj20221130 增加记录已经派工的记录数    
    ''' </summary>
    ''' <param name="containerid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateEngineStepdispatchCount(ByVal containerid As String) As Boolean Implements uLMSDispatchIBusiness.UpdateEngineStepdispatchCount
        UpdateEngineStepdispatchCount = False
        Dim strsql As String =
            <string>
                    update enginepartplan ep
                            set ep.stepdispatchcount = 
                                (select count(1) from containerstepdispatch cd where cd.containerid ='{0}' and cd.isuse = 0)
                    where ep.concontainerid = '{0}'
                </string>
        strsql = String.Format(strsql, containerid)
        DatabaseHelper.ExecuteSql(strsql)
        Return True
    End Function

    ''' <summary>
    ''' 派工后自动发起工装及辅料准备申请(工序) add zhunan 2022.12.8
    ''' </summary>
    ''' <param name="dtWorkflowstep"></param>
    ''' <param name="containerid"></param>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveSpecArrangementsAfterDispatching(ByVal dtWorkflowstep As DataTable, ByVal containerid As String, ByVal para As Dictionary(Of String, String)) As ArrayList Implements uLMSDispatchIBusiness.SaveSpecArrangementsAfterDispatching
        Dim sqlList As New ArrayList

        '所传递的 dtWorkflowstep 实参必须包含 WorkflowstepID 列
        If Not dtWorkflowstep.Columns.Contains("WorkflowstepID") Then
            Return sqlList
        End If

        If Not (para.ContainsKey("EmployeeID") And para("EmployeeID").ToString <> "") Then
            Return sqlList
        End If

        If Not (para.ContainsKey("TeamID") And para("TeamID").ToString <> "") Then
            Return sqlList
        End If

        If Not (para.ContainsKey("PlannedFinishDate") And para("PlannedFinishDate").ToString <> "") Then
            Return sqlList
        End If

        If Not (para.ContainsKey("arrangementsID2") And para("arrangementsID2").ToString <> "") Then
            Return sqlList
        End If

        Dim dtWorkflowstepList As DataTable = dtWorkflowstep.DefaultView.ToTable(True, "WorkflowstepID")
        dtWorkflowstepList.Columns.Add("MfgOrderName")
        dtWorkflowstepList.Columns.Add("ContainerID")
        dtWorkflowstepList.Columns.Add("WorkflowID")
        dtWorkflowstepList.Columns.Add("ContainerType")

        Dim dtContainerinfo = DatabaseHelper.Query_ReturnDatTable(String.Format("SELECT cci.containertype,cci.workflowid,cci.mfgordername FROM containercurrentinfo cci WHERE cci.containerid='{0}'", containerid))

        For Each dr As DataRow In dtWorkflowstepList.Rows
            If dtContainerinfo.Rows.Count > 0 Then
                dr("MfgOrderName") = dtContainerinfo.Rows(0)("mfgordername")
                dr("WorkflowID") = dtContainerinfo.Rows(0)("workflowid")
                dr("ContainerType") = dtContainerinfo.Rows(0)("containertype")
                dr("ContainerID") = containerid
            End If
            Dim sql As String = <string>
--夹模具表
SELECT WFS.WORKFLOWSTEPNAME,WFST.WORKFLOWSTEPID, WFST.QTY, WFST.SEQUENCE,ST.SPECIALTOOLID TOOLID, 
ST.SPECIALTOOLNAME TOOLNAME,ST.DESCRIPTION
  FROM WORKFLOWSTEPTOOLS WFST
  LEFT JOIN WORKFLOWSTEP WFS  ON WFS.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID
  LEFT JOIN SPECIALTOOL ST  ON ST.SPECIALTOOLID = WFST.TOOLS
  LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID AND A.TOOLNO = ST.SPECIALTOOLNAME AND A.CONTAINERID = '{0}' and A.STEPINFOID IS NULL
 WHERE ST.SPECIALTOOLID {1} ' ' AND WFST.WORKFLOWSTEPID = '{2}' AND A.ARRANGEMENTSID IS NULL
UNION ALL
--刀具
SELECT WFS.WORKFLOWSTEPNAME,WFST.WORKFLOWSTEPID, WFST.QTY, WFST.SEQUENCE,CT.CUTTOOLID TOOLID, 
CT.CUTTOOLNAME TOOLNAME,CT.DESCRIPTION
  FROM WORKFLOWSTEPTOOLS WFST
  LEFT JOIN WORKFLOWSTEP WFS  ON WFS.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID
  LEFT JOIN CUTTOOL CT  ON CT.CUTTOOLID = WFST.TOOLS
  LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID AND A.TOOLNO = CT.CUTTOOLNAME AND A.CONTAINERID = '{0}' and A.STEPINFOID IS NULL
   WHERE CT.CUTTOOLID {1} ' ' AND WFST.WORKFLOWSTEPID = '{2}' AND A.ARRANGEMENTSID IS NULL
UNION ALL
--量具表
SELECT WFS.WORKFLOWSTEPNAME,WFST.WORKFLOWSTEPID, WFST.QTY, WFST.SEQUENCE,MT.MEASURETOOLID TOOLID, 
MT.MEASURETOOLNAME TOOLNAME,MT.DESCRIPTION
  FROM WORKFLOWSTEPTOOLS WFST
  LEFT JOIN WORKFLOWSTEP WFS  ON WFS.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID
  LEFT JOIN MEASURETOOL MT  ON MT.MEASURETOOLID = WFST.TOOLS
  LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID AND A.TOOLNO = MT.MEASURETOOLNAME AND A.CONTAINERID = '{0}' and A.STEPINFOID IS NULL
   WHERE MT.MEASURETOOLID {1} ' ' AND WFST.WORKFLOWSTEPID ='{2}' AND A.ARRANGEMENTSID IS NULL
                            </string>.Value
            sql = String.Format(sql, containerid, "<>", dr("WorkflowstepID").ToString)
            '查询出工序工装工具是否申请
            Dim dt = DatabaseHelper.Query_ReturnDatTable(sql)
            Dim dic As New Dictionary(Of String, String)
            '如果存在没有申请的工序，则申请
            If dt.Rows.Count > 0 Then
                For Each item As DataRow In dt.Rows
                    dic.Clear()
                    dic.Add("ContainerID", containerid)
                    dic.Add("QTY", item("QTY").ToString)
                    dic.Add("ToolName", item("DESCRIPTION").ToString)
                    dic.Add("ToolNo", item("TOOLNAME").ToString)
                    dic.Add("ToolType", "LY14")
                    dic.Add("EmployeeID", para("EmployeeID"))
                    dic.Add("MfgOrderName", dr("MfgOrderName").ToString)
                    dic.Add("TeamID", para("TeamID"))
                    dic.Add("WorkflowstepID", dr("WorkflowstepID").ToString)
                    dic.Add("ArrangementsID2", para("arrangementsID2").ToString)
                    dic.Add("WorkflowID", dr("WorkflowID").ToString)
                    dic.Add("ContainerType", dr("ContainerType").ToString)
                    dic.Add("StepInfoID", "")
                    dic.Add("ApplyType", "0")
                    dic.Add("PlannedFinishDate", para("PlannedFinishDate")) '派工要求完成日期
                    sqlList.AddRange(GetInsertArrangementsSql(dic))
                Next
            End If
            '查询工序辅料是否申请
            sql = <string>
SELECT wst.WORKFLOWSTEPID, wst.QTY, wst.SEQUENCE,am.auxmaterialid,am.auxmaterialname,
am.description,am.specification,am.vendormodel
 FROM Workflowsteptools wst
LEFT JOIN workflowstep w  ON w.workflowstepid = wst.workflowstepid
inner join auxmaterial am on am.auxmaterialid = wst.tools
LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID =w.workflowstepid AND A.TOOLNO = am.auxmaterialname AND A.CONTAINERID = '{0}' and A.STEPINFOID IS NULL
WHERE w.workflowstepid = '{1}' AND A.Arrangementsid IS NULL 
                  </string>.Value
            sql = String.Format(sql, containerid, dr("WorkflowstepID").ToString)
            Dim dtaux = DatabaseHelper.Query_ReturnDatTable(sql)
            '如果存在未申请的辅料
            If dtaux.Rows.Count > 0 Then
                For Each item As DataRow In dtaux.Rows
                    dic.Clear()
                    dic.Add("ContainerID", containerid)
                    dic.Add("QTY", item("QTY").ToString)
                    dic.Add("ToolName", item("DESCRIPTION").ToString)
                    dic.Add("ToolNo", item("auxmaterialname").ToString)
                    dic.Add("ToolType", "LY14")
                    dic.Add("EmployeeID", para("EmployeeID"))
                    dic.Add("MfgOrderName", dr("MfgOrderName").ToString)
                    dic.Add("TeamID", para("TeamID"))
                    dic.Add("WorkflowstepID", dr("WorkflowstepID").ToString)
                    dic.Add("ArrangementsID2", para("arrangementsID2").ToString)
                    dic.Add("WorkflowID", dr("WorkflowID").ToString)
                    dic.Add("ContainerType", dr("ContainerType").ToString)
                    dic.Add("StepInfoID", "")
                    dic.Add("ApplyType", "5")
                    dic.Add("PlannedFinishDate", para("PlannedFinishDate")) '派工要求完成日期
                    sqlList.AddRange(GetInsertArrangementsSql(dic))
                Next
            End If
            '添加工步工装工具/辅料的生产准备
            sqlList.AddRange(SaveStepArrangementsAfterDispatching(dr, para))
        Next
        Return sqlList
    End Function

    ''' <summary>
    ''' 派工后自动发起工装及辅料准备申请(工步) add zhunan 2022.12.8
    ''' </summary>
    ''' <param name="stepDr"></param>
    ''' <param name="para"></param>
    ''' <param name="arrangementsID2"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveStepArrangementsAfterDispatching(ByVal stepDr As DataRow, ByVal para As Dictionary(Of String, String)) As ArrayList Implements uLMSDispatchIBusiness.SaveStepArrangementsAfterDispatching
        Dim sqlList As New ArrayList
        Dim sql As String = <string>
--夹模具表
SELECT sit.stepinfoid,s.description, sit.qty, sit.sequence,ST.SPECIALTOOLID TOOLID, 
ST.SPECIALTOOLNAME TOOLNAME,ST.DESCRIPTION TOOLDESCRIPTION
  FROM stepinfotools sit
  LEFT JOIN stepinfo s  ON s.stepinfoid = sit.stepinfoid
  LEFT JOIN SPECIALTOOL ST  ON ST.SPECIALTOOLID = sit.tools
  LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID = '{0}' AND A.TOOLNO = ST.SPECIALTOOLNAME AND A.CONTAINERID = '{1}' and A.STEPINFOID IS NOT NULL
 WHERE ST.SPECIALTOOLID {2} ' ' AND s.workflowstepid = '{0}' AND A.ARRANGEMENTSID IS NULL
UNION ALL
--刀具
SELECT sit.stepinfoid,s.description, sit.qty, sit.sequence,CT.CUTTOOLID TOOLID, 
CT.CUTTOOLNAME TOOLNAME,CT.DESCRIPTION TOOLDESCRIPTION
  FROM stepinfotools sit
  LEFT JOIN stepinfo s  ON s.stepinfoid = sit.stepinfoid
  LEFT JOIN CUTTOOL CT  ON CT.CUTTOOLID = sit.tools
  LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID = '{0}' AND A.TOOLNO = CT.CUTTOOLNAME AND A.CONTAINERID = '{1}' and A.STEPINFOID IS NOT NULL
 WHERE CT.CUTTOOLID {2} ' ' AND s.workflowstepid = '{0}' AND A.ARRANGEMENTSID IS NULL
UNION ALL
--量具表
SELECT sit.stepinfoid,s.description, sit.qty, sit.sequence,MT.MEASURETOOLID TOOLID, 
MT.MEASURETOOLNAME TOOLNAME,MT.DESCRIPTION TOOLDESCRIPTION
  FROM stepinfotools sit
  LEFT JOIN stepinfo s  ON s.stepinfoid = sit.stepinfoid
  LEFT JOIN MEASURETOOL MT  ON MT.MEASURETOOLID = sit.tools
  LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID = '{0}' AND A.TOOLNO = MT.MEASURETOOLNAME AND A.CONTAINERID = '{1}' and A.STEPINFOID IS NOT NULL
 WHERE MT.MEASURETOOLID {2} ' ' AND s.workflowstepid = '{0}' AND A.ARRANGEMENTSID IS NULL
                            </string>.Value
        sql = String.Format(sql, stepDr("WorkflowstepID").ToString, stepDr("ContainerID").ToString, "<>")
        '查询出工步工装工具是否申请
        Dim dt = DatabaseHelper.Query_ReturnDatTable(sql)
        '如果存在没有申请的工步，则申请
        Dim dic As New Dictionary(Of String, String)
        If dt.Rows.Count > 0 Then
            For Each item As DataRow In dt.Rows
                dic.Clear()
                dic.Add("ContainerID", stepDr("ContainerID").ToString)
                dic.Add("QTY", item("QTY").ToString)
                dic.Add("ToolName", item("TOOLDESCRIPTION").ToString)
                dic.Add("ToolNo", item("TOOLNAME").ToString)
                dic.Add("ToolType", "LY14")
                dic.Add("EmployeeID", para("EmployeeID"))
                dic.Add("MfgOrderName", stepDr("MfgOrderName").ToString)
                dic.Add("TeamID", para("TeamID"))
                dic.Add("WorkflowstepID", stepDr("WorkflowstepID").ToString)
                dic.Add("ArrangementsID2", para("arrangementsID2").ToString)
                dic.Add("WorkflowID", stepDr("WorkflowID").ToString)
                dic.Add("ContainerType", stepDr("ContainerType").ToString)
                dic.Add("StepInfoID", item("StepInfoID").ToString)
                dic.Add("ApplyType", "0")
                dic.Add("PlannedFinishDate", para("PlannedFinishDate")) '派工要求完成日期
                sqlList.AddRange(GetInsertArrangementsSql(dic))
            Next
        End If
        '查询工步辅料是否申请
        sql = <string>
SELECT sit.qty,sit.stepinfoid,sit.sequence, am.auxmaterialid,am.auxmaterialname,am.description,am.specification,am.vendormodel, am.notes
 FROM stepinfotools sit
LEFT JOIN stepinfo s  ON s.stepinfoid = sit.stepinfoid
inner join auxmaterial am on am.auxmaterialid = sit.tools
 LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID ='{1}' AND A.TOOLNO = am.auxmaterialname AND A.CONTAINERID = '{0}' and A.STEPINFOID IS NOT NULL
WHERE s.workflowstepid = '{1}' AND A.Arrangementsid IS NULL
                  </string>.Value
        sql = String.Format(sql, stepDr("ContainerID").ToString, stepDr("WorkflowstepID").ToString)
        Dim dtaux = DatabaseHelper.Query_ReturnDatTable(sql)
        '如果存在未申请的辅料
        If dtaux.Rows.Count > 0 Then
            For Each item As DataRow In dtaux.Rows
                dic.Clear()
                dic.Add("ContainerID", stepDr("ContainerID").ToString)
                dic.Add("QTY", item("QTY").ToString)
                dic.Add("ToolName", item("DESCRIPTION").ToString)
                dic.Add("ToolNo", item("auxmaterialname").ToString)
                dic.Add("ToolType", "LY14")
                dic.Add("EmployeeID", para("EmployeeID"))
                dic.Add("MfgOrderName", stepDr("MfgOrderName").ToString)
                dic.Add("TeamID", para("TeamID"))
                dic.Add("WorkflowstepID", stepDr("WorkflowstepID").ToString)
                dic.Add("ArrangementsID2", para("arrangementsID2").ToString)
                dic.Add("WorkflowID", stepDr("WorkflowID").ToString)
                dic.Add("ContainerType", stepDr("ContainerType").ToString)
                dic.Add("StepInfoID", item("StepInfoID").ToString)
                dic.Add("ApplyType", "5")
                dic.Add("PlannedFinishDate", para("PlannedFinishDate")) '派工要求完成日期
                sqlList.AddRange(GetInsertArrangementsSql(dic))
            Next
        End If
        Return sqlList
    End Function

    ''' <summary>
    ''' 生成插入生产准备信息sql add zhunan 2022.12.8
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetInsertArrangementsSql(ByVal para As Dictionary(Of String, String)) As ArrayList Implements uLMSDispatchIBusiness.GetInsertArrangementsSql
        Dim sqlList As New ArrayList
        Dim ID = Guid.NewGuid().ToString
        Dim strSQL As String = <string>
                    INSERT INTO ARRANGEMENTS
                      (CONTAINERID,QTY,TOOLNAME, TOOLNO, TOOLTYPE,TOOLSTATUS,CREATEDATE,ISINTEGRATION,
                        EMPLOYEEID, MFGORDERNAME, NEEDDATE, TEAMID,WORKFLOWSTEPID,ARRANGEMENTSID,ISTRANSFER,ARRANGEMENTSID2,WORKFLOWID,StepInfoID,ApplyType)
                    VALUES(</string>.Value
        strSQL += String.Format("'{0}',", para("ContainerID").ToString)
        strSQL += String.Format("'{0}',", para("QTY").ToString)
        strSQL += String.Format("'{0}',", para("ToolName").ToString)
        strSQL += String.Format("'{0}',", para("ToolNo").ToString)
        strSQL += String.Format("'{0}',", para("ToolType").ToString)
        strSQL += "'待准备',sysdate,'1',"
        strSQL += String.Format("'{0}',", para("EmployeeID"))
        strSQL += String.Format("'{0}',", para("MfgOrderName").ToString)
        strSQL += String.Format("to_date('{0}','yyyy/mm/dd'),", para("PlannedFinishDate"))
        strSQL += String.Format("'{0}',", para("TeamID"))
        strSQL += String.Format("'{0}',", para("WorkflowstepID").ToString)
        strSQL += String.Format("'{0}',", ID)
        strSQL += "0,"
        strSQL += String.Format("'{0}',", para("ArrangementsID2").ToString)
        strSQL += String.Format("'{0}',", para("WorkflowID").ToString)
        strSQL += String.Format("'{0}',", para("StepInfoID").ToString)
        strSQL += String.Format("'{0}')", para("ApplyType").ToString)
        sqlList.Add(strSQL)
        If para("ContainerType").ToString = "7" Then
            strSQL = <string>
                INSERT INTO ArrangementsContainer (ARRANGEMENTSID,CONTAINERID) VALUES ('{0}','{1}')
                 </string>.Value
            strSQL = String.Format(strSQL, ID, para("ContainerID").ToString)
            sqlList.Add(strSQL)
        End If
        Return sqlList
    End Function

#Region "自检自分 add by zhangrj 20230606"
    ''' <summary>
    ''' 填充自检自分的首件报工(只限当前序)
    ''' </summary>
    ''' <param name="disDto"></param>
    ''' <param name="workerId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function FillSelfCheckData(ByVal disDto As ContainerStepDispatchDTO, ByVal workerId As String) As List(Of Object()) Implements uLMSDispatchIBusiness.FillSelfCheckData
        Dim sqlStringList As New List(Of Object())
        Dim strSql = String.Format(<string>
                                       SELECT nvl(ws.controlcode,s.controlcode) controlcode,cci.workflowstepid
                                       FROM containercurrentinfo cci 
                                       LEFT JOIN  workflowstep ws ON ws.workflowstepid = cci.workflowstepid
                                       LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
                                       LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
                                       WHERE cci.containerid = '{0}'
                                       AND NOT EXISTS(SELECT 1 FROM containerstepworkreport csw 
                                       WHERE csw.containerid = '{0}' AND csw.workflowstepid = cci.workflowstepid AND csw.isuse = 0 AND csw.checktype = 1)
                     </string>.Value, disDto.ContainerID)
        Dim dtres = DatabaseHelper.Query_ReturnDatTable(strSql)
        '如果当前序是专检序，且派工数大于2，且没有过首件报工。则自动首件报工
        If dtres.Rows.Count > 0 Then
            If dtres.Rows(0)("workflowstepid").ToString = disDto.WorkflowstepID And String.IsNullOrWhiteSpace(dtres.Rows(0)("controlcode").ToString) And disDto.Qty > 2 Then
                Dim dto = New ContainerStepWorkReportDTO()
                dto.ContainerID = disDto.ContainerID
                dto.WorkflowstepID = disDto.WorkflowstepID
                dto.Qty = 1
                dto.WorkerID = workerId
                dto.FactoryID = disDto.FactoryID
                dto.WorkCenterID = disDto.WorkCenterID
                dto.ResourceID = ""
                dto.CheckerID = ""
                dto.FirstContainerNo = ""
                dto.KeyProcessBatchNo = ""
                dto.TeamID = disDto.TeamID
                dto.CheckType = 1
                dto.ContainerStepDispatchID = disDto.ContainerStepDispatchID
                dto.ContainerStepWorkReportID = Guid.NewGuid().ToString()
                sqlStringList.Add(m_ReportBusiness.FillContainerStepWorkReportInfoTrans(dto, "")) '插入报工记录
                sqlStringList.Add(m_ReportBusiness.FillUpdateStepCheckNum(disDto.ContainerID, 1)) '更新批次报工类型
                Dim para = New Dictionary(Of String, String)
                para.Add("type", "2")
                para.Add("WorkerID", dto.WorkerID)
                para.Add("ContainerStepDispatchID", disDto.ContainerStepDispatchID)
                sqlStringList.Add(m_ReportBusiness.FillReceviceDispatch(para)) '更新派工接收人
            End If
        End If
        Return sqlStringList
    End Function
#End Region

End Class
